
********************************************************************************************
* 2017
********************************************************************************************
clear all

use "$data\2017_H", clear


* Rename used variables
ren ER66156 moved
ren ER66002 id
ren ER66016 fsize
ren ER66017 age
ren ER66018 sex
ren ER66019 agew
ren ER66020 sexw
ren ER66021 kids
ren ER66022 newborn
ren ER66024 marit
ren ER70882 race1
ren ER70883 race2
ren ER70884 race3
ren ER70885 race4
ren ER70744 wrace1
ren ER70745 wrace2
ren ER70746 wrace3
ren ER70747 wrace4
ren ER67759 outkid
ren ER66031 house
ren ER71533 smsa
ren ER66007 fchg
ren ER66003 state
ren ER71570 weight 
ren ER66164 empst1
ren ER66165 empst2
ren ER66166 empst3
ren ER66439 wempst1
ren ER66440 wempst2
ren ER66441 wempst3
ren ER71538 educ
ren ER71539 weduc
ren ER66766 foodstamps
ren ER71227 weeks_emp
ren ER71248 weeks_empw
ren ER66231 exp_job_years
ren ER66232 exp_job_months
ren ER66233 exp_job_weeks
ren ER66369 unempl 
ren ER66362 layoff
ren ER70752 sp_veteran
ren ER70890 veteran
ren ER66195 occupation

ren ER71339 tr_pen
ren ER71341 tr_ann
ren ER71343 tr_ira
ren ER71345 tr_ret
ren ER71347 tr_unem
ren ER71349 tr_comp
ren ER71353 tr_alim
ren ER71357 tr_oth
ren ER71359 tr_misc

ren ER71367 tr_vapw
ren ER71369 tr_penw
ren ER71371 tr_annw
ren ER71373 tr_iraw
ren ER71375 tr_retw
ren ER71377 tr_unemw
ren ER71383 tr_alimw
ren ER71385 tr_helpw
ren ER71387 tr_othw
ren ER71389 tr_miscw


ren ER71340 tr_pen_acc
ren ER71342 tr_ann_acc
ren ER71344 tr_ira_acc
ren ER71346 tr_ret_acc

ren ER71354 tr_alim_acc
ren ER71358 tr_oth_acc
ren ER71360 tr_misc_acc

ren ER71348 tr_unem_acc
ren ER71378 tr_unemw_acc
ren ER71350 tr_comp_acc


ren ER68424 disab
ren ER68425 twdisab
ren ER68426 hmdisab
ren ER69551 sp_disab
ren ER69552 sp_twdisab
ren ER69553 sp_hmdisab


ren ER70943 workyears
ren ER70944 workyears_full
ren ER70806 spworkyears_full 
ren ER70805 spworkyears

replace weeks_emp =. if weeks_emp == 98 | weeks_emp ==99
replace exp_job_years =. if exp_job_years == 98 | exp_job_years ==99
replace exp_job_months =. if exp_job_months == 98 | exp_job_months ==99
replace exp_job_weeks =. if exp_job_weeks == 98 | exp_job_weeks ==99

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace layoff =. if layoff == 8 | layoff == 9
replace layoff =0 if layoff == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99

///HEALTH MEASURES ///
ren ER68514 trouble_walk
ren ER68515 trouble_bend
ren ER68511 in_hosp
ren ER68512 days_in_hosp

ren ER68464   arthritis
ren ER68449   asthma
ren ER68444  bloodp
ren ER68479  cancer
ren ER68459  diabetes  
ren ER68487  emotion
ren ER68433  heart_att
ren ER68439  heart_dis
ren ER68454  lung_dis
ren ER68427  stroke    

ren ER68537  trouble_meals
ren ER68538  trouble_meals_health
ren ER68539 trouble_shop
ren ER68540 trouble_shop_health
ren ER68541 trouble_money
ren ER68542 trouble_money_health
ren ER68543 trouble_phone
ren ER68544 trouble_phone_health
ren ER68545 trouble_heavywork
ren ER68546 trouble_heavywork_health
ren ER68547 trouble_lightwork
ren ER68548 trouble_lightwork_health

/// ASSETS ///

* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren ER71429 bus_ass
ren ER71430 bus_ass_acc

ren ER71431 bus_debt
ren ER71432 bus_debt_acc

ren ER71435 cash
ren ER71436 cash_acc  

ren ER71439 real_estate
ren ER71440 real_estate_acc

ren ER71441 real_estate_debt
ren ER71442 real_estate_debt_acc

ren ER71445 stocks
ren ER71446 stocks_acc

ren ER71447 vehicles
ren ER71448 vehicles_acc

ren ER71451 other_ass
ren ER71452 other_ass_acc

ren ER71455 ira
ren ER71456 ira_acc

ren ER71459 credit_card 
ren ER71460 credit_card_acc

ren ER71463 stu_debt
ren ER71464 stu_debt_acc

ren ER71467 med_debt
ren ER71468 med_debt_acc

ren ER71471 legal_debt
ren ER71472 legal_debt_acc

ren ER71475 fam_debt
ren ER71476 fam_debt_acc

ren ER71479 other_debt 
ren ER71480 other_debt_acc

/* ^^^^ Note this includes student, legal, medical and fam debt */

ren ER71481 home_equity
ren ER71482 home_equity_acc

ren ER71485 w_with_h
ren ER71484 w_with_h_acc



/* ^^^^ Constructed wealth variable, including equity. This imputed variable is constructed as
the sum of values of seven asset types (ER65352, ER65358, ER65362, ER65368, ER65370,
ER65374, ER65378) net of debt value (ER65354, ER65364, ER65382, ER65386, ER65390, ER65394,
ER65398, ER65402) plus value of home equity (ER65404). All missing data were assigned. */

ren ER68010 pension_plan
replace pension_plan=. if pension_plan>999999997
ren ER68227 pension_sp
replace pension_sp=. if pension_sp>999999997

/// EXPENDITURE ///

gen       fstmp=0
cap program drop doit
program def doit
        local i=66770
        while `i' < ER66782 {
            replace ER`i'=. if ER`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(ER66770-ER66781)
replace fstmp=ER66767              if ER66768==6 
replace fstmp=ER66767*nm           if ER66768==5 
replace fstmp=ER66767*((26/12)*nm) if ER66768==4  
replace fstmp=ER66767*((52/12)*nm) if ER66768==3
replace fstmp=.					 if ER66767>999997  | ER66768>6 
drop nm


ren ER66787 fdhm1
ren ER66788 fdhmpER1
ren ER66797 fdhm2
ren ER66798 fdhmpER2
ren ER66791 fddel1
ren ER66801 fddel2
ren ER66792 fddelpER1
ren ER66802 fddelpER2
ren ER66794 fout1
ren ER66804 fout2
ren ER66795 foutpER1
ren ER66805 foutpER2
ren ER66782 fstamp

* Note that these are taken from the imputed values, _acc variables denote imputed entries
ren ER71488 food_home
ren ER71489 food_out
ren ER71490 food_deliv

ren ER71492 mortgage  
ren ER66053 mortgage_raw1
ren ER66074 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER71494 rent
ren ER66090 rent_raw
ren ER66091 rent_per
gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER71495 prop_tax 
ren ER66045 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER71496 homeinsure
ren ER66047 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren ER71497 utilities
ren ER66111 gas_raw
ren ER66113 electric_raw
ren ER66117 water_raw
ren ER66123 otheru_raw
gen utilities_acc = 0
replace utilities_acc = 1 if gas_raw >= 9999999 | electric_raw >= 9999999 | water_raw >= 9999999 | otheru_raw >= 9999999 

rename ER66114  electric_per
rename ER66112  gas_per
rename ER66118 water_per
rename ER66124  miscutils_per
ren ER66120 telecom_per

ren ER71502 telecom
ren ER66119 telecom_raw
gen telecom_acc = 0
replace telecom_acc = 1 if telecom_raw>=9998

ren ER66927  carinsper

ren ER71504 vehicle_loan
ren ER66869 vehicle_loan_raw1 
ren ER66893 vehicle_loan_raw2
ren ER66917 vehicle_loan_raw3
gen vehicle_loan_acc = 0
replace vehicle_loan_acc = 1 if vehicle_loan_raw1 >=999998 | vehicle_loan_raw2 >=999998 | vehicle_loan_raw3 >=999998 

ren ER71505 vehicle_down
ren ER66866 vehicle_down_raw1
ren ER66890 vehicle_down_raw2
ren ER66914 vehicle_down_raw3
gen vehicle_down_acc = 0
replace vehicle_down_acc = 1 if vehicle_down_raw1 >= 999998 | vehicle_down_raw2 >= 999998 | vehicle_down_raw3 >= 999998 

ren ER71506 vehicle_lease
ren ER66873 vehicle_lease_raw1
ren ER66897 vehicle_lease_raw2
ren ER66921 vehicle_lease_raw3
gen vehicle_lease_acc = 0
replace vehicle_lease_acc = 1 if vehicle_lease_raw1 >=999998 | vehicle_lease_raw2 >=999998 |vehicle_lease_raw3 >=999998 

ren ER71507 auto_ins
ren ER66926 auto_ins_raw
gen auto_ins_acc = 0
replace auto_ins_acc = 1 if auto_ins_raw >= 999998

ren ER71508 other_vehicle
ren ER66930 other_vehicle_raw
gen other_vehicle_acc = 0
replace other_vehicle_acc  = 1 if other_vehicle_raw >=999998

ren ER71510 gasoline
ren ER66931 gasoline_raw
gen gasoline_acc = 0
replace gasoline_acc = 1 if gasoline_raw >= 99998

ren ER71509 auto_repair
ren ER66929 auto_repair_raw
gen auto_repair_acc = 0
replace auto_repair_acc = 1 if auto_repair_raw >=99998

ren ER71511 parking
ren ER66932 parking_raw
gen parking_acc = 0
replace parking_acc = 1 if parking_raw >= 99998 

ren ER71512 bus_train
ren ER66933 bus_train_raw
gen bus_train_acc = 0
replace bus_train_acc = 1 if bus_train_raw >= 99998

ren ER71513 taxi
ren ER66934 taxi_raw
gen taxi_acc = 0
replace taxi_acc = 1 if taxi_raw >= 99998

ren ER71514 other_trans
ren ER66935 other_trans_raw
gen other_trans_acc = 0
replace other_trans_acc = 1 if other_trans_raw >=99998

ren ER71515 education
ren ER66937 education_raw1
ren ER66939 education_raw2
gen education_acc = 0
replace education_acc = 1 if education_raw1 >= 999998 | education_raw2 >= 999998

ren ER71516 childcare
ren ER66744 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

ren ER71518 inst_medical
ren ER70689 inst_medical_raw
gen inst_medical_acc = 0
replace inst_medical_acc = 1 if inst_medical_raw >= 999998

ren ER71519 doctor
ren ER70694 doctor_raw
gen doctor_acc = 0
replace doctor_acc = 1 if doctor_raw >= 999998

ren ER71520 prescription
ren ER70698 prescription_raw
gen prescription_acc = 0
replace prescription_acc = 1 if prescription_raw >= 999998

ren ER71521 health_ins
ren ER70683 health_ins_raw
gen health_ins_acc = 0
replace health_ins_acc = 1 if health_ins_raw >= 99998

ren ER71523 home_repair
ren ER66944 home_repair_raw
gen home_repair_acc = 0
replace home_repair_acc = 1 if home_repair_raw >= 999998

ren ER71524 home_furnish
ren ER66949 home_furnish_raw
gen home_furnish_acc = 0
replace home_furnish_acc = 1 if home_furnish_raw >= 999998

ren ER71525 clothing
ren ER66954 clothing_raw
gen clothing_acc = 0
replace clothing_acc = 1 if clothing_raw >= 999998

ren ER71526 trips
ren ER66959 trips_raw
gen trips_acc = 0
replace trips_acc = 1 if trips_raw >= 999998

ren ER71527 other_rec
ren ER66964 other_rec_raw
gen other_rec_acc = 0
replace other_rec_acc = 1 if other_rec >= 999998


/// INCOME ///

ren ER71426 y
gen truncy=y<1
replace y=1 if y<1


ren ER71330 tyhw
ren ER71391 trhw
ren ER71398 tyoth
ren ER71419 troth
ren ER71420 soc_sec
ren ER71422 soc_secw
ren ER71424 soc_secyoth



* Asset Income *

ren ER71294 renty
ren ER71295 renty_acc
ren ER71322 rentyw
ren ER71323 rentyw_acc

ren ER71296 divi
ren ER71297 divi_acc
ren ER71324 diviw
ren ER71325 diviw_acc

ren ER71298 interest
ren ER71299 interest_acc
ren ER71326 interestw
ren ER71327 interestw_acc

ren ER71300 trustinc
ren ER71301 trustinc_acc
ren ER71328 trustincw
ren ER71329 trustincw_acc

ren ER71275 businc
ren ER71303 busincw

* Generate labor and asset part of farm income *
gen farmlabor=0
replace farmlabor=0.5*ER71272 			if ER71272>0
replace farmlabor=. 					if ER71272>9999997

gen farmasset=0
replace farmasset=0.5*ER71272 			if ER71272>0
replace farmasset=ER71272 				if ER71272<0
replace farmasset=. 					if ER71272>9999997

* Hours Worked *
ren ER71233 hours
ren ER71254 hourw

* Income *
ren ER71293 wages
ren ER71321 wagesw
ren ER71274 y_business
ren ER71302 yw_business

egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)


ren ER70684 hinsuranceper
ren ER66198 self

ren ER67768 chsupp_paid

/// Prep variables to save ///

#delimit;

keep 

moved
id fsize age sex agew sexw kids marit race* wrace* *disab* sp_veteran veteran occupation
outkid house smsa fchg state weight *educ newborn tr_*

bus_ass* bus_debt* cash* real_estate* real_estate_debt* stocks* vehicles* other_ass*
ira* other_debt* home_equity* w_with_h* self*

chsupp_paid

fstmp food_home food_out food_deliv fd* fo* fstamp mortgage rent prop_tax homeinsure utilities telecom
vehicle_loan vehicle_down vehicle_lease auto_ins other_vehicle gasoline auto_repair
parking bus_train taxi other_trans education* childcare inst_medical doctor prescription
health_ins home_repair home_furnish clothing trips other_rec

y truncy tyhw trhw tyoth troth soc_sec soc_secw soc_secyoth
farmlabor farmasset hours* wages* y_business yw_business ly wly weeks_emp* hourw
exp_job_years exp_job_months exp_job_weeks empst* wempst* unempl layoff
rent* divi* interest* trust* bus* *per *raw *workyears*
arthritis asthma bloodp cancer diabetes emotion heart_att heart_dis lung_dis stroke
trouble*

; 

#delimit cr

gen year=2017

save "$temp\HH_2017.dta", replace


********************************************************************************************
* 2015 
********************************************************************************************
clear all

use "$data\2015_H", clear

* Rename used variables
ren ER60155 moved
ren ER60002 id
ren ER60016 fsize
ren ER60017 age
ren ER60018 sex
ren ER60019 agew
ren ER60020 sexw
ren ER60021 kids
ren ER60022 newborn
ren ER60024 marit
ren ER64810 race1
ren ER64811 race2
ren ER64812 race3
ren ER64813 race4
ren ER64671 wrace1
ren ER64672 wrace2
ren ER64673 wrace3
ren ER64674 wrace4
ren ER61706 outkid
ren ER60031 house
ren ER65454 smsa
ren ER60007 fchg
ren ER60003 state
ren ER65492 weight 
ren ER60163 empst1
ren ER60164 empst2
ren ER60165 empst3
ren ER60426 wempst1
ren ER60427 wempst2
ren ER60428 wempst3
ren ER65459 educ
ren ER65460 weduc
ren ER60719 foodstamps
ren ER65150 weeks_emp
ren ER65171 weeks_empw
ren ER60228 exp_job_years
ren ER60229 exp_job_months
ren ER60230 exp_job_weeks
ren ER60366 unempl 
ren ER60359 layoff
ren ER65270  tr_unem
ren ER65300  tr_unemw
ren ER65272  tr_comp
ren ER64679 sp_veteran
ren ER64818 veteran
ren ER60194 occupation

ren ER65271 tr_unem_acc
ren ER65301 tr_unemw_acc
ren ER65273 tr_comp_acc


ren ER62370 disab
ren ER62371 twdisab
ren ER62372 hmdisab
ren ER63486 sp_disab
ren ER63487 sp_twdisab
ren ER63488 sp_hmdisab


ren ER64871 workyears
ren ER64872 workyears_full
ren ER64733 spworkyears_full 
ren ER64732 spworkyears


replace weeks_emp =. if weeks_emp == 98 | weeks_emp ==99
replace exp_job_years =. if exp_job_years == 98 | exp_job_years ==99
replace exp_job_months =. if exp_job_months == 98 | exp_job_months ==99
replace exp_job_weeks =. if exp_job_weeks == 98 | exp_job_weeks ==99

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace layoff =. if layoff == 8 | layoff == 9
replace layoff =0 if layoff == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99

///HEALTH MEASURES ///
ren ER62448 in_hosp
ren ER62449 days_in_hosp

ren ER62410 arthritis
ren ER62395 asthma
ren ER62390 bloodp
ren ER62425 cancer
ren ER62405 diabetes  
ren ER62433 emotion
ren ER62379 heart_att
ren ER62385 heart_dis
ren ER62400 lung_dis
ren ER62373 stroke    

ren ER62472 trouble_meals
ren ER62473 trouble_meals_health
ren ER62474 trouble_shop
ren ER62475 trouble_shop_health
ren ER62476 trouble_money
ren ER62477 trouble_money_health
ren ER62478 trouble_phone
ren ER62479 trouble_phone_health
ren ER62480 trouble_heavywork
ren ER62481 trouble_heavywork_health
ren ER62482 trouble_lightwork
ren ER62483 trouble_lightwork_health

/// ASSETS ///

* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren ER65352 bus_ass
ren ER65353 bus_ass_acc

ren ER65354 bus_debt
ren ER65355 bus_debt_acc

ren ER65358 cash
ren ER65359 cash_acc  

ren ER65362 real_estate
ren ER65363 real_estate_acc

ren ER65364 real_estate_debt
ren ER65365 real_estate_debt_acc

ren ER65368 stocks
ren ER65369 stocks_acc

ren ER65370 vehicles
ren ER65371 vehicles_acc

ren ER65374 other_ass
ren ER65375 other_ass_acc

ren ER65378 ira
ren ER65379 ira_acc

ren ER65382 credit_card 
ren ER65383 credit_card_acc

ren ER65386 stu_debt
ren ER65387 stu_debt_acc

ren ER65390 med_debt
ren ER65391 med_debt_acc

ren ER65394 legal_debt
ren ER65395 legal_debt_acc

ren ER65398 fam_debt
ren ER65399 fam_debt_acc

ren ER65402 other_debt 
ren ER65403 other_debt_acc

/* ^^^^ Note this includes student, legal, medical and fam debt */

ren ER65404 home_equity
ren ER65405 home_equity_acc

ren ER65408 w_with_h
ren ER65409 w_with_h_acc




/* ^^^^ Constructed wealth variable, including equity. This imputed variable is constructed as
the sum of values of seven asset types (ER65352, ER65358, ER65362, ER65368, ER65370,
ER65374, ER65378) net of debt value (ER65354, ER65364, ER65382, ER65386, ER65390, ER65394,
ER65398, ER65402) plus value of home equity (ER65404). All missing data were assigned. */

ren ER61956 pension_plan
replace pension_plan=. if pension_plan>999999997
ren ER62173 pension_sp
replace pension_sp=. if pension_sp>999999997

/// EXPENDITURE ///

gen       fstmp=0
cap program drop doit
program def doit
        local i=60723
        while `i' < 60735 {
            replace ER`i'=. if ER`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(ER60723-ER60734)
replace fstmp=ER60720              if ER60721==6 
replace fstmp=ER60720*nm           if ER60721==5 
replace fstmp=ER60720*((26/12)*nm) if ER60721==4  
replace fstmp=ER60720*((52/12)*nm) if ER60721==3
replace fstmp=.					 if ER60720>999997  | ER60721>6 
drop nm


ren ER60740 fdhm1
ren ER60741 fdhmpER1
ren ER60750 fdhm2
ren ER60751  fdhmpER2
ren ER60744  fddel1
ren ER60754 fddel2
ren ER60745 fddelpER1
ren ER60755 fddelpER2
ren ER60747 fout1
ren ER60757 fout2
ren ER60748  foutpER1
ren ER60758 foutpER2
ren ER60735   fstamp

* Note that these are taken from the imputed values, _acc variables denote imputed entries
ren ER65411 food_home
ren ER65412 food_out
ren ER65413 food_deliv

ren ER65415 mortgage  
ren ER60051 mortgage_raw1
ren ER60072 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER65416 rent
ren ER60088 rent_raw
ren ER60089 rent_per

gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER65417 prop_tax 
ren ER60043 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER65418 homeinsure
ren ER60045 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren ER65419 utilities
ren ER60110  gas_raw
ren ER60112  electric_raw
ren ER60116  water_raw
ren ER60122  otheru_raw
gen utilities_acc = 0
replace utilities_acc = 1 if gas_raw >= 9999999 | electric_raw >= 9999999 | water_raw >= 9999999 | otheru_raw >= 9999999 


rename ER60113  electric_per
rename ER60111  gas_per
rename ER60117 water_per
rename ER60123  miscutils_per


ren ER65424 telecom
ren ER60118 telecom_raw
ren ER60119 telecom_per
gen telecom_acc = 0
replace telecom_acc = 1 if telecom_raw>=9998

ren ER60879  carinsper


ren ER65426 vehicle_loan
ren ER60821 vehicle_loan_raw1 
ren ER60845 vehicle_loan_raw2
ren ER60869 vehicle_loan_raw3
gen vehicle_loan_acc = 0
replace vehicle_loan_acc = 1 if vehicle_loan_raw1 >=999998 | vehicle_loan_raw2 >=999998 | vehicle_loan_raw3 >=999998 

ren ER65427 vehicle_down
ren ER60818 vehicle_down_raw1
ren ER60842 vehicle_down_raw2
ren ER60866 vehicle_down_raw3
gen vehicle_down_acc = 0
replace vehicle_down_acc = 1 if vehicle_down_raw1 >= 999998 | vehicle_down_raw2 >= 999998 | vehicle_down_raw3 >= 999998 

ren ER65428 vehicle_lease
ren ER60825 vehicle_lease_raw1
ren ER60849 vehicle_lease_raw2
ren ER60873 vehicle_lease_raw3
gen vehicle_lease_acc = 0
replace vehicle_lease_acc = 1 if vehicle_lease_raw1 >=999998 | vehicle_lease_raw2 >=999998 |vehicle_lease_raw3 >=999998 

ren ER65429 auto_ins
ren ER60878 auto_ins_raw
gen auto_ins_acc = 0
replace auto_ins_acc = 1 if auto_ins_raw >= 999998

ren ER65430 other_vehicle
ren ER60882 other_vehicle_raw
gen other_vehicle_acc = 0
replace other_vehicle_acc  = 1 if other_vehicle_raw >=999998

ren ER65432 gasoline
ren ER60883 gasoline_raw
gen gasoline_acc = 0
replace gasoline_acc = 1 if gasoline_raw >= 99998

ren ER65431 auto_repair
ren ER60881 auto_repair_raw
gen auto_repair_acc = 0
replace auto_repair_acc = 1 if auto_repair_raw >=99998

ren ER65433 parking
ren ER60884 parking_raw
gen parking_acc = 0
replace parking_acc = 1 if parking_raw >= 99998 

ren ER65434 bus_train
ren ER60885 bus_train_raw
gen bus_train_acc = 0
replace bus_train_acc = 1 if bus_train_raw >= 99998

ren ER65435 taxi
ren ER60886 taxi_raw
gen taxi_acc = 0
replace taxi_acc = 1 if taxi_raw >= 99998

ren ER65436 other_trans
ren ER60887 other_trans_raw
gen other_trans_acc = 0
replace other_trans_acc = 1 if other_trans_raw >=99998

ren ER65437 education
ren ER60889 education_raw1
ren ER60891 education_raw2
gen education_acc = 0
replace education_acc = 1 if education_raw1 >= 999998 | education_raw2 >= 999998

ren ER65438 childcare
ren ER60697 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

ren ER65440 inst_medical
ren ER64613 inst_medical_raw
gen inst_medical_acc = 0
replace inst_medical_acc = 1 if inst_medical_raw >= 999998

ren ER65441 doctor
ren ER64619 doctor_raw
gen doctor_acc = 0
replace doctor_acc = 1 if doctor_raw >= 999998

ren ER65442 prescription
ren ER64625 prescription_raw
gen prescription_acc = 0
replace prescription_acc = 1 if prescription_raw >= 999998

ren ER65443 health_ins
ren ER64607 health_ins_raw
gen health_ins_acc = 0
replace health_ins_acc = 1 if health_ins_raw >= 99998

ren ER65444 home_repair
ren ER60892 home_repair_raw
gen home_repair_acc = 0
replace home_repair_acc = 1 if home_repair_raw >= 999998

ren ER65445 home_furnish
ren ER60897 home_furnish_raw
gen home_furnish_acc = 0
replace home_furnish_acc = 1 if home_furnish_raw >= 999998

ren ER65446 clothing
ren ER60902 clothing_raw
gen clothing_acc = 0
replace clothing_acc = 1 if clothing_raw >= 999998

ren ER65447 trips
ren ER60907 trips_raw
gen trips_acc = 0
replace trips_acc = 1 if trips_raw >= 999998

ren ER65448 other_rec
ren ER60912 other_rec_raw
gen other_rec_acc = 0
replace other_rec_acc = 1 if other_rec >= 999998


/// INCOME ///

ren ER65349 y
gen truncy=y<1
replace y=1 if y<1


ren ER65253 tyhw
ren ER65314 trhw
ren ER65321 tyoth
ren ER65342 troth
ren ER65343 soc_sec
ren ER65345 soc_secw
ren ER65347 soc_secyoth



* Asset Income *

ren ER65217 renty
ren ER65218 renty_acc
ren ER65245 rentyw
ren ER65246 rentyw_acc

ren ER65219 divi
ren ER65220 divi_acc
ren ER65247 diviw
ren ER65248 diviw_acc

ren ER65221 interest
ren ER65222 interest_acc
ren ER65249 interestw
ren ER65250 interestw_acc

ren ER65223 trustinc
ren ER65224 trustinc_acc
ren ER65251 trustincw
ren ER65252 trustincw_acc

ren ER65198 businc
ren ER65226 busincw


* Generate labor and asset part of farm income *
gen farmlabor=0
replace farmlabor=0.5*ER65195 			if ER65195>0
replace farmlabor=. 					if ER65195>9999997

gen farmasset=0
replace farmasset=0.5*ER65195 			if ER65195>0
replace farmasset=ER65195 				if ER65195<0
replace farmasset=. 					if ER65195>9999997

* Hours Worked *
ren ER65156 hours
ren ER65177 hourw

* Income *
ren ER65216 wages
ren ER65244 wagesw
ren ER65197 y_business
ren ER65225 yw_business

egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)

ren ER64608 hinsuranceper
ren ER60197 self
ren ER61715  chsupp_paid


/// Prep variables to save ///

#delimit;

keep 

moved
id fsize age sex agew sexw kids marit race* wrace* *disab* sp_veteran veteran occupation
outkid house smsa fchg state weight *educ newborn 

bus_ass* bus_debt* cash* real_estate* real_estate_debt* stocks* vehicles* other_ass*
ira* other_debt* home_equity* w_with_h* self*
chsupp

fstmp food_home food_out food_deliv fd* fo* fstamp mortgage rent prop_tax homeinsure utilities telecom
vehicle_loan vehicle_down vehicle_lease auto_ins other_vehicle gasoline auto_repair
parking bus_train taxi other_trans education* childcare inst_medical doctor prescription
health_ins home_repair home_furnish clothing trips other_rec 

y truncy tyhw trhw tyoth troth soc_sec soc_secw soc_secyoth
farmlabor farmasset hours* wages* y_business yw_business ly wly weeks_emp* hourw
exp_job_years exp_job_months exp_job_weeks empst* wempst* unempl layoff
rent* divi* interest* trust* bus* *per *raw tr_comp tr_unemw tr_unem *workyears*
arthritis asthma bloodp cancer diabetes emotion heart_att heart_dis lung_dis stroke trouble*

; 

#delimit cr

gen year=2015

save "$temp\HH_2015.dta", replace


********************************************************************************************
* 2013
********************************************************************************************

use "$data\2013_H", clear

ren ER53140 moved
ren ER53002 id
ren ER53016 fsize
ren ER53017 age
ren ER53018 sex
ren ER53019 agew
ren ER53020 kids
ren ER53021 newborn
ren ER53023 marit
ren ER57659 race1
ren ER57660 race2
ren ER57661 race3
ren ER57662 race4
ren ER57549 wrace1
ren ER57550 wrace2
ren ER57551 wrace3
ren ER57552 wrace4
ren ER54595 outkid
ren ER53030 house
ren ER58218 smsa
ren ER53007 fchg
ren ER53003 state
ren ER58257 weight 
ren ER53148 empst1
ren ER53149 empst2
ren ER53150 empst3
ren ER53411 wempst1
ren ER53412 wempst2
ren ER53413 wempst3
ren ER53704 foodstamps
ren ER58223 educ
ren ER58224 weduc
ren ER57970 weeks_emp
ren ER57991 weeks_empw
ren ER53213 exp_job_years
ren ER53214 exp_job_months
ren ER53215 exp_job_weeks
ren ER53351 unempl 
ren ER53344 layoff
ren ER58077  tr_unem
ren ER58105  tr_unemw
ren ER58079  tr_comp
ren ER57556 sp_veteran
ren ER57666  veteran
ren ER53179 occupation


ren ER58078 tr_unem_acc
ren ER58106 tr_unemw_acc
ren ER58080 tr_comp_acc


ren ER55248 disab
ren ER55249 twdisab
ren ER55250 hmdisab
ren ER56364 sp_disab
ren ER56365 sp_twdisab
ren ER56366 sp_hmdisab

ren ER57711 workyears
ren ER57712 workyears_full
ren ER57602 spworkyears_full 
ren ER57601 spworkyears


replace weeks_emp =. if weeks_emp == 98 | weeks_emp ==99
replace exp_job_years =. if exp_job_years == 98 | exp_job_years ==99
replace exp_job_months =. if exp_job_months == 98 | exp_job_months ==99
replace exp_job_weeks =. if exp_job_weeks == 98 | exp_job_weeks ==99


replace educ =. if educ == 99
replace weduc =. if weduc == 99

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace layoff =. if layoff == 8 | layoff == 9
replace layoff =0 if layoff == 5

///HEALTH MEASURES ///
ren ER55326 in_hosp
ren ER55327 days_in_hosp

ren ER55288 arthritis
ren ER55273 asthma
ren ER55268 bloodp
ren ER55303 cancer
ren ER55283 diabetes  
ren ER55311 emotion
ren ER55257 heart_att
ren ER55263 heart_dis
ren ER55278 lung_dis
ren ER55251 stroke    

ren ER55350 trouble_meals
ren ER55351 trouble_meals_health
ren ER55352 trouble_shop
ren ER55353 trouble_shop_health
ren ER55354 trouble_money
ren ER55355 trouble_money_health
ren ER55356 trouble_phone
ren ER55357 trouble_phone_health
ren ER55358 trouble_heavywork
ren ER55359 trouble_heavywork_health
ren ER55360 trouble_lightwork
ren ER55361 trouble_lightwork_health


/// ASSETS ///

* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren ER58155 bus_ass
ren ER58156 bus_ass_acc

ren ER58157 bus_debt
ren ER58158 bus_debt_acc

ren ER58161 cash
ren ER58162 cash_acc  

ren ER58165 real_estate
ren ER58166 real_estate_acc

ren ER58167 real_estate_debt
ren ER58168 real_estate_debt_acc

ren ER58171 stocks
ren ER58172 stocks_acc

ren ER58173 vehicles
ren ER58174 vehicles_acc

ren ER58177 other_ass
ren ER58178 other_ass_acc

ren ER58181 ira
ren ER58182 ira_acc

ren ER58185 credit_card
ren ER58186 credit_card_acc

ren ER58189 stu_debt
ren ER58190 stu_debt_acc

ren ER58193 med_debt
ren ER58194 med_debt_acc

ren ER58197 legal_debt
ren ER58198 legal_debt_acc

ren ER58201 fam_debt
ren ER58202 fam_debt_acc

ren ER58205 other_debt 
ren ER58206 other_debt_acc

/* ^^^^ Note this includes student, legal, medical and fam debt */

ren ER58207 home_equity
ren ER58208 home_equity_acc

ren ER58211 w_with_h
ren ER58212 w_with_h_acc

ren ER53725 fdhm1
ren ER53726 fdhmpER1
ren ER53735 fdhm2
ren ER53736  fdhmpER2
ren ER53729  fddel1
ren ER53739 fddel2
ren ER53730 fddelpER1
ren ER53740 fddelpER2
ren ER53732 fout1
ren ER53742 fout2
ren ER53733  foutpER1
ren ER53743 foutpER2
ren ER53720   fstamp


/* ^^^^ Constructed wealth variable, including equity. This imputed variable is constructed as
the sum of values of seven asset types (ER65352, ER65358, ER65362, ER65368, ER65370,
ER65374, ER65378) net of debt value (ER65354, ER65364, ER65382, ER65386, ER65390, ER65394,
ER65398, ER65402) plus value of home equity (ER65404). All missing data were assigned. */

ren ER54836 pension_plan
replace pension_plan=. if pension_plan>999999997
ren ER55052 pension_sp
replace pension_sp=. if pension_sp>999999997

/// EXPENDITURE ///

gen       fstmp=0
cap program drop doit
program def doit
        local i=53708
        while `i' < 53720 {
            replace ER`i'=. if ER`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(ER53708-ER53719)
replace fstmp=ER53705              if ER53706==6 
replace fstmp=ER53705*nm           if ER53706==5 
replace fstmp=ER53705*((26/12)*nm) if ER53706==4  
replace fstmp=ER53705*((52/12)*nm) if ER53706==3
replace fstmp=.					 if ER53705>999997  | ER53706>6 
drop nm


* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren ER58212A2 food_home
ren ER58212A3 food_out
ren ER58212A4 food_deliv

ren ER58212A6 mortgage  
ren ER53050 mortgage_raw1
ren ER53071 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER58212A7 rent
ren ER53087 rent_raw
ren ER53088 rent_per
gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER58212A8 prop_tax 
ren ER53042 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER58212A9 homeinsure
ren ER53044 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren ER58212B1 utilities
ren ER53115  gas_raw
ren ER53117  electric_raw
ren ER53121  water_raw
ren ER53127  otheru_raw
gen utilities_acc = 0
replace utilities_acc = 1 if gas_raw >= 9999999 | electric_raw >= 9999999 | water_raw >= 9999999 | otheru_raw >= 9999999 

rename ER53118  electric_per
rename ER53116  gas_per
rename ER53122 water_per
rename ER53128  miscutils_per

ren ER58212B6 telecom
ren ER53123 telecom_raw
ren ER53124 telecom_per
gen telecom_acc = 0
replace telecom_acc = 1 if telecom_raw>=9998

ren ER53820  carinsper

ren ER58212B8 vehicle_loan
ren ER53762 vehicle_loan_raw1 
ren ER53786 vehicle_loan_raw2
ren ER53810 vehicle_loan_raw3
gen vehicle_loan_acc = 0
replace vehicle_loan_acc = 1 if vehicle_loan_raw1 >=999998 | vehicle_loan_raw2 >=999998 | vehicle_loan_raw3 >=999998 

ren ER58212B9 vehicle_down
ren ER53759 vehicle_down_raw1
ren ER53783 vehicle_down_raw2
ren ER53807 vehicle_down_raw3
gen vehicle_down_acc = 0
replace vehicle_down_acc = 1 if vehicle_down_raw1 >= 999998 | vehicle_down_raw2 >= 999998 | vehicle_down_raw3 >= 999998 

ren ER58212C1 vehicle_lease
ren ER53766 vehicle_lease_raw1
ren ER53790 vehicle_lease_raw2
ren ER53814 vehicle_lease_raw3
gen vehicle_lease_acc = 0
replace vehicle_lease_acc = 1 if vehicle_lease_raw1 >=999998 | vehicle_lease_raw2 >=999998 |vehicle_lease_raw3 >=999998 

ren ER58212C2 auto_ins
ren ER53819 auto_ins_raw
gen auto_ins_acc = 0
replace auto_ins_acc = 1 if auto_ins_raw >= 999998

ren ER58212C3 other_vehicle
ren ER53822 other_vehicle_raw
gen other_vehicle_acc = 0
replace other_vehicle_acc  = 1 if other_vehicle_raw >=999998

ren ER58212C5 gasoline
ren ER53824 gasoline_raw
gen gasoline_acc = 0
replace gasoline_acc = 1 if gasoline_raw >= 99998

ren ER58212C4 auto_repair
ren ER53823 auto_repair_raw
gen auto_repair_acc = 0
replace auto_repair_acc = 1 if auto_repair_raw >=99998

ren ER58212C6 parking
ren ER53825 parking_raw
gen parking_acc = 0
replace parking_acc = 1 if parking_raw >= 99998 

ren ER58212C7 bus_train
ren ER53826 bus_train_raw
gen bus_train_acc = 0
replace bus_train_acc = 1 if bus_train_raw >= 99998

ren ER58212C8 taxi
ren ER53827 taxi_raw
gen taxi_acc = 0
replace taxi_acc = 1 if taxi_raw >= 99998

ren ER58212C9 other_trans
ren ER53828 other_trans_raw
gen other_trans_acc = 0
replace other_trans_acc = 1 if other_trans_raw >=99998

ren ER58212D1 education
ren ER53830 education_raw1
ren ER53832 education_raw2
gen education_acc = 0
replace education_acc = 1 if education_raw1 >= 999998 | education_raw2 >= 999998

ren ER58212D2 childcare
ren ER53682 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

ren ER58212D4 inst_medical
ren ER57491 inst_medical_raw
gen inst_medical_acc = 0
replace inst_medical_acc = 1 if inst_medical_raw >= 999998



ren ER58212D7 health_ins
ren ER57485 health_ins_raw
gen health_ins_acc = 0
replace health_ins_acc = 1 if health_ins_raw >= 99998

ren ER58212D8 home_repair
ren ER53833 home_repair_raw
gen home_repair_acc = 0
replace home_repair_acc = 1 if home_repair_raw >= 999998

ren ER58212D9 home_furnish
ren ER53838 home_furnish_raw
gen home_furnish_acc = 0
replace home_furnish_acc = 1 if home_furnish_raw >= 999998

ren ER58212E1 clothing
ren ER53843 clothing_raw
gen clothing_acc = 0
replace clothing_acc = 1 if clothing_raw >= 999998

ren ER58212E2 trips
ren ER53848 trips_raw
gen trips_acc = 0
replace trips_acc = 1 if trips_raw >= 999998

ren ER58212E3 other_rec
ren ER53853 other_rec_raw
gen other_rec_acc = 0
replace other_rec_acc = 1 if other_rec >= 999998


/// INCOME ///

ren ER58152 y
gen truncy=y<1
replace y=1 if y<1

ren ER58060 tyhw
ren ER58117 trhw
ren ER58124 tyoth
ren ER58145 troth
ren ER58146 soc_sec
ren ER58148 soc_secw
ren ER58150 soc_secyoth



* Asset Income *

ren ER58039 renty
ren ER58040 renty_acc
ren ER58052 rentyw
ren ER58053 rentyw_acc

ren ER58041 divi
ren ER58042 divi_acc
ren ER58054 diviw
ren ER58055 diviw_acc

ren ER58043 interest
ren ER58044 interest_acc
ren ER58056 interestw
ren ER58057 interestw_acc

ren ER58045 trustinc
ren ER58046 trustinc_acc
ren ER58058 trustincw
ren ER58059 trustincw_acc

ren ER58018 businc
ren ER58048 busincw

* Generate labor and asset part of farm income *
gen farmlabor=0
replace farmlabor=0.5*ER58015 			if ER58015>0
replace farmlabor=. 					if ER58015>9999997

gen farmasset=0
replace farmasset=0.5*ER58015 			if ER58015>0
replace farmasset=ER58015 				if ER58015<0
replace farmasset=. 					if ER58015>9999997

* Hours Worked *
ren ER57976 hours
ren ER57997 hourw

* Income *
ren ER58038 wages
ren ER58050 wagesw
ren ER58017 y_business
ren ER58047 yw_business

egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)

ren ER57486 hinsuranceper
ren ER57503  prescription
ren ER57497  doctor
ren ER53182  self


/// Prep variables to save ///
ren ER58032 gardasset
ren ER54604  chsupp_paid


#delimit;

keep 

moved
id fsize age sex agew kids marit race* wrace* *disab* sp_veteran veteran occupation
outkid house smsa fchg state weight foodstamps *educ newborn

bus_ass* bus_debt* cash* real_estate* real_estate_debt* stocks* vehicles* other_ass*
ira* other_debt* home_equity* w_with_h* self*

gardasset chsupp

fstmp food_home food_out food_deliv fd* fo* fstamp mortgage rent prop_tax homeinsure utilities telecom
vehicle_loan vehicle_down vehicle_lease auto_ins other_vehicle gasoline auto_repair
parking bus_train taxi other_trans education* childcare inst_medical doctor prescription
health_ins home_repair home_furnish clothing trips other_rec

y truncy tyhw trhw tyoth troth soc_sec soc_secw soc_secyoth
farmlabor farmasset hours* wages* y_business yw_business ly wly weeks_emp* hourw
exp_job_years exp_job_months exp_job_weeks empst* wempst* unempl layoff
rent* divi* interest* trust* bus* *per *raw  tr_comp tr_unemw tr_unem *workyears*
arthritis asthma bloodp cancer diabetes emotion heart_att heart_dis lung_dis stroke trouble*

; 

#delimit cr


gen year=2013

save "$temp\HH_2013.dta", replace

********************************************************************************************
* 2011
********************************************************************************************

use "$data\2011_H", clear

ren ER47440 moved
ren ER47302 id
ren ER47316 fsize
ren ER47317 age
ren ER47318 sex
ren ER47319 agew
ren ER47320 kids
ren ER47321 newborn
ren ER47323 marit
ren ER51904 race1
ren ER51905 race2
ren ER51906 race3
ren ER51907 race4
ren ER51810 wrace1
ren ER51811 wrace2
ren ER51812 wrace3
ren ER51813 wrace4
ren ER48852 outkid
ren ER47330 house
ren ER52400 smsa
ren ER47307 fchg
ren ER47303 state
ren ER52436 weight 
ren ER47448 empst1
ren ER47449 empst2
ren ER47450 empst3
ren ER47705 wempst1
ren ER47706 wempst2
ren ER47707 wempst3
ren ER48007 foodstamps
ren ER52405 educ
ren ER52406 weduc
ren ER52169 weeks_emp
ren ER52190 weeks_empw
ren ER47513 exp_job_years
ren ER47514 exp_job_months
ren ER47515 exp_job_weeks
ren ER47651 unempl 
ren ER47644 layoff
ren ER52274  tr_unem
ren ER52296  tr_unemw
ren ER52276 tr_comp
ren ER51817 sp_veteran
ren ER51911 veteran
ren ER47479 occupation


ren ER52275 tr_unem_acc
ren ER52297 tr_unemw_acc
ren ER52277 tr_comp_acc


ren ER49498 disab
ren ER49499 twdisab
ren ER49500 hmdisab
ren ER50616 sp_disab
ren ER50617 sp_twdisab
ren ER50618 sp_hmdisab


ren ER51955 workyears
ren ER51956 workyears_full
ren ER51862 spworkyears_full 
ren ER51861 spworkyears


replace weeks_emp =. if weeks_emp == 98 | weeks_emp ==99
replace exp_job_years =. if exp_job_years == 98 | exp_job_years ==99
replace exp_job_months =. if exp_job_months == 98 | exp_job_months ==99
replace exp_job_weeks =. if exp_job_weeks == 98 | exp_job_weeks ==99

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace layoff =. if layoff == 8 | layoff == 9
replace layoff =0 if layoff == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99

///HEALTH MEASURES ///
ren ER49578 in_hosp
ren ER49579 days_in_hosp

ren ER49538 arthritis
ren ER49523 asthma
ren ER49518 bloodp
ren ER49553 cancer
ren ER49533 diabetes  
ren ER49562 emotion
ren ER49507 heart_att
ren ER49513 heart_dis
ren ER49528 lung_dis
ren ER49501 stroke    

ren ER49602 trouble_meals
ren ER49603 trouble_meals_health
ren ER49604 trouble_shop
ren ER49605 trouble_shop_health
ren ER49606 trouble_money
ren ER49607 trouble_money_health
ren ER49608 trouble_phone
ren ER49609 trouble_phone_health
ren ER49610 trouble_heavywork
ren ER49611 trouble_heavywork_health
ren ER49612 trouble_lightwork
ren ER49613 trouble_lightwork_health

/// ASSETS ///

* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren ER52346 bus_ass
ren ER52347 bus_ass_acc
gen bus_debt =.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren ER52350 cash
ren ER52351 cash_acc  

ren ER52354 real_estate
ren ER52355 real_estate_acc
gen real_estate_debt=.

/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren ER52358 stocks
ren ER52359 stocks_acc

ren ER52360 vehicles
ren ER52361 vehicles_acc

ren ER52364 other_ass
ren ER52365 other_ass_acc

ren ER52368 ira
ren ER52369 ira_acc

ren ER52372 credit_card
ren ER52373 credit_card_acc

ren ER52376 stu_debt
ren ER52377 stu_debt_acc

ren ER52380 med_debt
ren ER52381 med_debt_acc

ren ER52384 legal_debt
ren ER52385 legal_debt_acc

ren ER52388 fam_debt
ren ER52389 fam_debt_acc

gen other_debt = credit_card + stu_debt + med_debt + legal_debt + fam_debt
/* ^^^^ Note "other debt" to be calculated manually */

ren ER52390 home_equity
ren ER52391 home_equity_acc

ren ER52394 w_with_h
ren ER52395 w_with_h_acc



/* ^^^^ Constructed wealth variable, including equity. This imputed variable is constructed as
the sum of values of seven asset types (ER65352, ER65358, ER65362, ER65368, ER65370,
ER65374, ER65378) net of debt value (ER65354, ER65364, ER65382, ER65386, ER65390, ER65394,
ER65398, ER65402) plus value of home equity (ER65404). All missing data were assigned. */

ren ER49080 pension_plan
replace pension_plan=. if pension_plan>999999997
ren ER49299 pension_sp
replace pension_sp=. if pension_sp>999999997

/// EXPENDITURE ///

gen       fstmp=0
cap program drop doit
program def doit
        local i=48011
        while `i' < 48023 {
            replace ER`i'=. if ER`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(ER48011-ER48022)
replace fstmp=ER48008              if ER48009==6 
replace fstmp=ER48008*nm           if ER48009==5 
replace fstmp=ER48008*((26/12)*nm) if ER48009==4  
replace fstmp=ER48008*((52/12)*nm) if ER48009==3
replace fstmp=.					 if ER48008>999997  | ER48009>6 
drop nm


ren ER48028 fdhm1
ren ER48029 fdhmpER1
ren ER48038 fdhm2
ren ER48039 fdhmpER2
ren ER48032  fddel1
ren ER48042 fddel2
ren ER48033 fddelpER1
ren ER48043 fddelpER2
ren ER48035 fout1
ren ER48045 fout2
ren ER48036  foutpER1
ren ER48046 foutpER2
ren ER48023   fstamp
* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren ER52395A2 food_home
ren ER52395A3 food_out
ren ER52395A4 food_deliv

/* ^^^^ STILL NEED TO DEAL WITH FOOD STAMPS / UTILITY SUBSIDY */

ren ER52395A6 mortgage  
ren ER47350 mortgage_raw1
ren ER47371 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER52395A7 rent
ren ER47387 rent_raw
ren ER47388 rent_per
gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER52395A8 prop_tax 
ren ER47342 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER52395A9 homeinsure
ren ER47344 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren ER52395B1 utilities
ren ER47415  gas_raw
ren ER47417 electric_raw
ren ER47421  water_raw
ren ER47427 otheru_raw
gen utilities_acc = 0
replace utilities_acc = 1 if gas_raw >= 9999999 | electric_raw >= 9999999 | water_raw >= 9999999 | otheru_raw >= 9999999 

rename ER47418  electric_per
rename ER47416  gas_per
rename ER47422 water_per
rename ER47428  miscutils_per

ren ER52395B6 telecom
ren ER47423 telecom_raw
ren ER47424 telecom_per
gen telecom_acc = 0
replace telecom_acc = 1 if telecom_raw>=9998

ren ER48126  carinsper

ren ER52395B8 vehicle_loan
ren ER48066 vehicle_loan_raw1 
ren ER48091 vehicle_loan_raw2
ren ER48116 vehicle_loan_raw3
gen vehicle_loan_acc = 0
replace vehicle_loan_acc = 1 if vehicle_loan_raw1 >=999998 | vehicle_loan_raw2 >=999998 | vehicle_loan_raw3 >=999998 

ren ER52395B9 vehicle_down
ren ER48063 vehicle_down_raw1
ren ER48088 vehicle_down_raw2
ren ER48113 vehicle_down_raw3
gen vehicle_down_acc = 0
replace vehicle_down_acc = 1 if vehicle_down_raw1 >= 999998 | vehicle_down_raw2 >= 999998 | vehicle_down_raw3 >= 999998 

ren ER52395C1 vehicle_lease
ren ER48070 vehicle_lease_raw1
ren ER48095 vehicle_lease_raw2
ren ER48120 vehicle_lease_raw3
gen vehicle_lease_acc = 0
replace vehicle_lease_acc = 1 if vehicle_lease_raw1 >=999998 | vehicle_lease_raw2 >=999998 |vehicle_lease_raw3 >=999998 

ren ER52395C2 auto_ins
ren ER48125 auto_ins_raw
gen auto_ins_acc = 0
replace auto_ins_acc = 1 if auto_ins_raw >= 999998

ren ER52395C3 other_vehicle
ren ER48128 other_vehicle_raw
gen other_vehicle_acc = 0
replace other_vehicle_acc  = 1 if other_vehicle_raw >=999998

ren ER52395C5 gasoline
ren ER48130 gasoline_raw
gen gasoline_acc = 0
replace gasoline_acc = 1 if gasoline_raw >= 99998

ren ER52395C4 auto_repair
ren ER48129 auto_repair_raw
gen auto_repair_acc = 0
replace auto_repair_acc = 1 if auto_repair_raw >=99998

ren ER52395C6 parking
ren ER48131 parking_raw
gen parking_acc = 0
replace parking_acc = 1 if parking_raw >= 99998 

ren ER52395C7 bus_train
ren ER48132 bus_train_raw
gen bus_train_acc = 0
replace bus_train_acc = 1 if bus_train_raw >= 99998

ren ER52395C8 taxi
ren ER48133 taxi_raw
gen taxi_acc = 0
replace taxi_acc = 1 if taxi_raw >= 99998

ren ER52395C9 other_trans
ren ER48134 other_trans_raw
gen other_trans_acc = 0
replace other_trans_acc = 1 if other_trans_raw >=99998

ren ER52395D1 education
ren ER48136 education_raw1
ren ER48138 education_raw2
gen education_acc = 0
replace education_acc = 1 if education_raw1 >= 999998 | education_raw2 >= 999998

ren ER52395D2 childcare
ren ER47970 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

ren ER52395D4 inst_medical


ren ER52395D7 health_ins

ren ER52395D8 home_repair
ren ER48139 home_repair_raw
gen home_repair_acc = 0
replace home_repair_acc = 1 if home_repair_raw >= 999998

ren ER52395D9 home_furnish
ren ER48144 home_furnish_raw
gen home_furnish_acc = 0
replace home_furnish_acc = 1 if home_furnish_raw >= 999998

ren ER52395E1 clothing
ren ER48149 clothing_raw
gen clothing_acc = 0
replace clothing_acc = 1 if clothing_raw >= 999998

ren ER52395E2 trips
ren ER48154 trips_raw
gen trips_acc = 0
replace trips_acc = 1 if trips_raw >= 999998

ren ER52395E3 other_rec
ren ER48159 other_rec_raw
gen other_rec_acc = 0
replace other_rec_acc = 1 if other_rec >= 999998


/// INCOME ///

ren ER52343 y
gen truncy=y<1
replace y=1 if y<1

ren ER52259 tyhw
ren ER52308 trhw
ren ER52315 tyoth
ren ER52336 troth
ren ER52337 soc_sec
ren ER52339 soc_secw
ren ER52341 soc_secyoth



* Asset Income *

ren ER52238 renty
ren ER52239 renty_acc
ren ER52251 rentyw
ren ER52252 rentyw_acc

ren ER52240 divi
ren ER52241 divi_acc
ren ER52253 diviw
ren ER52254 diviw_acc

ren ER52242 interest
ren ER52243 interest_acc
ren ER52255 interestw
ren ER52256 interestw_acc

ren ER52244 trustinc
ren ER52245 trustinc_acc
ren ER52257 trustincw
ren ER52258 trustincw_acc

ren ER52217 businc
ren ER52247 busincw

* Generate labor and asset part of farm income *
gen farmlabor=0
replace farmlabor=0.5*ER52214 			if ER52214>0
replace farmlabor=. 					if ER52214>9999997

gen farmasset=0
replace farmasset=0.5*ER52214 			if ER52214>0
replace farmasset=ER52214 				if ER52214<0
replace farmasset=. 					if ER52214>9999997

* Hours Worked *
ren ER52175 hours
ren ER52196 hourw

* Income *
ren ER52237 wages
ren ER52249 wagesw
ren ER52216 y_business
ren ER52246 yw_business

egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)


ren ER51744 hinsurance
ren ER51748 nurse
ren ER51754  doctor
ren ER51760  prescription
ren ER51765 totalhealth
ren ER47482  self


ren ER52231 gardasset
ren ER48295 roomerinc
ren ER48296  roomerper
ren ER48861  chsupp_paid

/// Prep variables to save ///

#delimit;

keep 

moved
id fsize age sex agew kids marit race* wrace* *disab* sp_veteran veteran occupation
outkid house smsa fchg state weight foodstamps *educ newborn

gardasset roomerinc roomerper chsupp

bus_ass* bus_debt* cash* real_estate* real_estate_debt* stocks* vehicles* other_ass*
ira* other_debt* home_equity* w_with_h* self*

fstmp food_home food_out food_deliv fd* fo* fstamp mortgage rent prop_tax homeinsure utilities telecom
vehicle_loan vehicle_down vehicle_lease auto_ins other_vehicle gasoline auto_repair
parking bus_train taxi other_trans education* childcare inst_medical doctor prescription
health_ins home_repair home_furnish clothing trips other_rec hinsurance nurse

y truncy tyhw trhw tyoth troth soc_sec soc_secw soc_secyoth
farmlabor farmasset hours* wages* y_business yw_business ly wly weeks_emp* hourw
exp_job_years exp_job_months exp_job_weeks empst* wempst* unempl layoff
rent* divi* interest* trust* bus* *per *raw tr_comp tr_unemw tr_unem *workyears*
arthritis asthma bloodp cancer diabetes emotion heart_att heart_dis lung_dis stroke trouble*

; 

#delimit cr


gen year=2011

save "$temp\HH_2011.dta", replace


********************************************************************************************
* 2009
********************************************************************************************

use "$data\2009_H", clear

ren ER42132 moved
ren ER42002 id
ren ER42016 fsize
ren ER42017 age
ren ER42018 sex
ren ER42019 agew
ren ER42020 kids
ren ER42021 newborn
ren ER42023 marit
ren ER46543 race1
ren ER46544 race2
ren ER46545 race3
ren ER46546 race4
ren ER46449 wrace1
ren ER46450 wrace2
ren ER46451 wrace3
ren ER46452 wrace4
ren ER43527 outkid
ren ER42030 house
ren ER46976 smsa  
ren ER42007 fchg
ren ER42003 state
ren ER47012 weight 
ren ER42140 empst1
ren ER42141 empst2
ren ER42142 empst3
ren ER42392 wempst1
ren ER42393 wempst2
ren ER42394 wempst3
ren ER42691 foodstamps
ren ER46981 educ
ren ER46982 weduc
ren ER46761 weeks_emp
ren ER46782 weeks_empw
ren ER42200 exp_job_years
ren ER42201 exp_job_months
ren ER42202 exp_job_weeks
ren ER42338 unempl 
ren ER42331 layoff 
ren ER46866  tr_unem
ren ER46888  tr_unemw
ren ER46868 tr_comp
ren ER46456 sp_veteran
ren ER46550  veteran
ren ER42167 occupation

ren ER46867 tr_unem_acc
ren ER46889 tr_unemw_acc
ren ER46869 tr_comp_acc



ren ER44179 disab
ren ER44180 twdisab 
ren ER44181 hmdisab

ren ER45276 sp_disab
ren ER45277 sp_twdisab
ren ER45278 sp_hmdisab

ren ER46594 workyears
ren ER46595 workyears_full
ren ER46501 spworkyears_full 
ren ER46500 spworkyears


replace weeks_emp =. if weeks_emp == 98 | weeks_emp ==99
replace exp_job_years =. if exp_job_years == 98 | exp_job_years ==99
replace exp_job_months =. if exp_job_months == 98 | exp_job_months ==99
replace exp_job_weeks =. if exp_job_weeks == 98 | exp_job_weeks ==99

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace layoff =. if layoff == 8 | layoff == 9
replace layoff =0 if layoff == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99

///HEALTH MEASURES ///
ren ER44240 in_hosp
ren ER44241 days_in_hosp

ren ER44210 arthritis
ren ER44198 asthma
ren ER44194 bloodp
ren ER44222 cancer
ren ER44206 diabetes  
ren ER44229 emotion
ren ER44186 heart_att
ren ER44190 heart_dis
ren ER44202 lung_dis
ren ER44182 stroke    

ren ER44264 trouble_meals
ren ER44265 trouble_meals_health
ren ER44266 trouble_shop
ren ER44267 trouble_shop_health
ren ER44268 trouble_money
ren ER44269 trouble_money_health
ren ER44270 trouble_phone
ren ER44271 trouble_phone_health
ren ER44272 trouble_heavywork
ren ER44273 trouble_heavywork_health
ren ER44274 trouble_lightwork
ren ER44275 trouble_lightwork_health


/// ASSETS ///

* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren ER46938 bus_ass
ren ER46939 bus_ass_acc
gen bus_debt =.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren ER46942 cash
ren ER46943 cash_acc  

ren ER46950 real_estate
ren ER46951 real_estate_acc
gen real_estate_debt=.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren ER46954 stocks
ren ER46955 stocks_acc

ren ER46956 vehicles
ren ER46957 vehicles_acc

ren ER46960 other_ass
ren ER46961 other_ass_acc

ren ER46964 ira
ren ER46965 ira_acc

ren ER46946 other_debt
ren ER46947 other_debt_acc // Credit card charges, student loans, medical or legal bills, or loans from relatives?

/* ^^^^ Note "other debt" to be calculated manually after 2009*/

ren ER46966 home_equity
ren ER46967 home_equity_acc

ren ER46970 w_with_h
ren ER46971 w_with_h_acc


/* ^^^^ Constructed wealth variable, including equity. This imputed variable is constructed as
sum of values of seven asset types (ER46938, ER46942, ER46950, ER46954, ER46956, ER46960,
ER46964) net of debt value (ER46946) plus value of home equity (ER46966). All missing data
were assigned. */

ren ER43734 pension_plan
replace pension_plan=. if pension_plan>999999997

/* No data on wife pension pre 2011 */ 

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=42695
        while `i' < 42707 {
            replace ER`i'=. if ER`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(ER42695-ER42706)
replace fstmp=ER42692              if ER42693==6 
replace fstmp=ER42692*nm           if ER42693==5 
replace fstmp=ER42692*((26/12)*nm) if ER42693==4  
replace fstmp=ER42692*((52/12)*nm) if ER42693==3
replace fstmp=.					 if ER42692>999997 | ER42693>6 | ER42693==1 
drop nm

ren ER42712 fdhm1
ren ER42713 fdhmpER1
ren ER42722 fdhm2
ren ER42723  fdhmpER2
ren ER42716  fddel1
ren ER42726 fddel2
ren ER42717 fddelpER1
ren ER42727 fddelpER2
ren ER42719 fout1
ren ER42729 fout2
ren ER42720  foutpER1
ren ER42730 foutpER2
ren ER42707   fstamp
* Note that these are taken from the imputed values, _acc variables denote imputed entries
ren ER46971A2 food_home
ren ER46971A3 food_out
ren ER46971A4 food_deliv

ren ER46971A6 mortgage  
ren ER42045 mortgage_raw1
ren ER42064 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER46971A7 rent
ren ER42080 rent_raw
ren ER42081 rent_per
gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER46971A8 prop_tax 
ren ER42037 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER46971A9 homeinsure
ren ER42039 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren ER46971B1 utilities
ren ER42112  gas_raw
ren ER42114  electric_raw
ren ER42118  water_raw
ren ER42124  otheru_raw
gen utilities_acc = 0
replace utilities_acc = 1 if gas_raw >= 9999999 | electric_raw >= 9999999 | water_raw >= 9999999 | otheru_raw >= 9999999 


rename ER42115  electric_per
rename ER42113 gas_per
rename ER42119 water_per
rename ER42125  miscutils_per



ren ER46971B6 telecom
ren ER42120 telecom_raw
ren ER42121 telecom_per
gen telecom_acc = 0
replace telecom_acc = 1 if telecom_raw>=9998

ren ER42804  carinsper

ren ER46971B8 vehicle_loan
ren ER42748 vehicle_loan_raw1 
ren ER42771 vehicle_loan_raw2
ren ER42794 vehicle_loan_raw3
gen vehicle_loan_acc = 0
replace vehicle_loan_acc = 1 if vehicle_loan_raw1 >=999998 | vehicle_loan_raw2 >=999998 | vehicle_loan_raw3 >=999998 

ren ER46971B9 vehicle_down
ren ER42745 vehicle_down_raw1
ren ER42768 vehicle_down_raw2
ren ER42791 vehicle_down_raw3
gen vehicle_down_acc = 0
replace vehicle_down_acc = 1 if vehicle_down_raw1 >= 999998 | vehicle_down_raw2 >= 999998 | vehicle_down_raw3 >= 999998 

ren ER46971C1 vehicle_lease
ren ER42752 vehicle_lease_raw1
ren ER42775 vehicle_lease_raw2
ren ER42798 vehicle_lease_raw3
gen vehicle_lease_acc = 0
replace vehicle_lease_acc = 1 if vehicle_lease_raw1 >=999998 | vehicle_lease_raw2 >=999998 |vehicle_lease_raw3 >=999998 

ren ER46971C2 auto_ins
ren ER42803 auto_ins_raw
gen auto_ins_acc = 0
replace auto_ins_acc = 1 if auto_ins_raw >= 999998

ren ER46971C3 other_vehicle
ren ER42806 other_vehicle_raw
gen other_vehicle_acc = 0
replace other_vehicle_acc  = 1 if other_vehicle_raw >=999998

ren ER46971C5 gasoline
ren ER42808 gasoline_raw
gen gasoline_acc = 0
replace gasoline_acc = 1 if gasoline_raw >= 99998

ren ER46971C4 auto_repair
ren ER42807 auto_repair_raw
gen auto_repair_acc = 0
replace auto_repair_acc = 1 if auto_repair_raw >=99998

ren ER46971C6 parking
ren ER42809 parking_raw
gen parking_acc = 0
replace parking_acc = 1 if parking_raw >= 99998 

ren ER46971C7 bus_train
ren ER42810 bus_train_raw
gen bus_train_acc = 0
replace bus_train_acc = 1 if bus_train_raw >= 99998

ren ER46971C8 taxi
ren ER42811 taxi_raw
gen taxi_acc = 0
replace taxi_acc = 1 if taxi_raw >= 99998

ren ER46971C9 other_trans
ren ER42812 other_trans_raw
gen other_trans_acc = 0
replace other_trans_acc = 1 if other_trans_raw >=99998

ren ER46971D1 education
ren ER42814 education_raw1
ren ER42816 education_raw2
gen education_acc = 0
replace education_acc = 1 if education_raw1 >= 999998 | education_raw2 >= 999998

ren ER46971D2 childcare
ren ER42652 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

ren ER46971D7 health_ins

ren ER46971D8 home_repair
ren ER42817 home_repair_raw
gen home_repair_acc = 0
replace home_repair_acc = 1 if home_repair_raw >= 999998

ren ER46971D9 home_furnish
ren ER42822 home_furnish_raw
gen home_furnish_acc = 0
replace home_furnish_acc = 1 if home_furnish_raw >= 999998

ren ER46971E1 clothing
ren ER42827 clothing_raw
gen clothing_acc = 0
replace clothing_acc = 1 if clothing_raw >= 999998

ren ER46971E2 trips
ren ER42832 trips_raw
gen trips_acc = 0
replace trips_acc = 1 if trips_raw >= 999998

ren ER46971E3 other_rec
ren ER42837 other_rec_raw
gen other_rec_acc = 0
replace other_rec_acc = 1 if other_rec >= 999998


/// INCOME ///

ren ER46935 y
gen truncy=y<1
replace y=1 if y<1

ren ER46851 tyhw
ren ER46900 trhw
ren ER46907 tyoth
ren ER46928 troth
ren ER46929 soc_sec
ren ER46931 soc_secw
ren ER46933 soc_secyoth



* Asset Income *

ren ER46830 renty
ren ER46831 renty_acc
ren ER46843 rentyw
ren ER46844 rentyw_acc

ren ER46832 divi
ren ER46833 divi_acc
ren ER46845 diviw
ren ER46846 diviw_acc

ren ER46834 interest
ren ER46835 interest_acc
ren ER46847 interestw
ren ER46848 interestw_acc

ren ER46836 trustinc
ren ER46837 trustinc_acc
ren ER46849 trustincw
ren ER46850 trustincw_acc

ren ER46809 businc
ren ER46839 busincw

* Generate labor and asset part of farm income *
gen farmlabor=0
replace farmlabor=0.5*ER46806 			if ER46806>0
replace farmlabor=. 					if ER46806>9999997

gen farmasset=0
replace farmasset=0.5*ER46806 			if ER46806>0
replace farmasset=ER46806 				if ER46806<0
replace farmasset=. 					if ER46806>9999997

* Hours Worked *
ren ER46767 hours
ren ER46788 hourw

* Income *
ren ER46829 wages
ren ER46841 wagesw
ren ER46808 y_business
ren ER46838 yw_business

egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)

ren ER46383  hinsurance
ren ER46387  nurse
ren ER46393 doctor
ren ER46399  prescription
ren ER46404  totalhealth
ren ER42169 self

/// Prep variables to save ///
ren ER46823 gardasset
ren ER42973 roomerinc
ren ER42974  roomerper

ren ER43536  chsupp_paid
/// Prep variables to save ///

#delimit;

keep 

moved
id fsize age sex agew kids marit race* wrace* *disab* *disab* sp_veteran veteran occupation
outkid house smsa fchg state weight foodstamps *educ newborn

gardasset roomerinc roomerper chsupp

bus_ass* bus_debt* cash* real_estate* real_estate_debt* stocks* vehicles* other_ass*
ira* other_debt* home_equity* w_with_h* self*

fstmp food_home fd* fo* fstamp food_out food_deliv mortgage rent prop_tax homeinsure utilities telecom
vehicle_loan vehicle_down vehicle_lease auto_ins other_vehicle gasoline auto_repair
parking bus_train taxi other_trans education* childcare doctor prescription
health_ins home_repair home_furnish clothing trips other_rec hinsurance nurse 

y truncy tyhw trhw tyoth troth soc_sec soc_secw soc_secyoth
farmlabor farmasset hours* wages* y_business yw_business ly wly weeks_emp* hourw
exp_job_years exp_job_months exp_job_weeks empst* wempst* unempl layoff
rent* divi* interest* trust* bus* *per *raw tr_comp tr_unemw tr_unem *workyears*
arthritis asthma bloodp cancer diabetes emotion heart_att heart_dis lung_dis stroke trouble*

; 

#delimit cr


gen year=2009

save "$temp\HH_2009.dta", replace


********************************************************************************************
* 2007
********************************************************************************************

use "$data\2007_H", clear
rename ER36002 S801
merge 1:1 S801 using "$data\2007_W.dta"
rename S801 ER36002

ren ER36103 moved
ren ER36002 id
ren ER36016 fsize
ren ER36017 age
ren ER36018 sex
ren ER36019 agew
ren ER36020 kids
ren ER36021 newborn
ren ER36023 marit
ren ER40565 race1
ren ER40566 race2
ren ER40567 race3
ren ER40568 race4
ren ER40472 wrace1
ren ER40473 wrace2
ren ER40474 wrace3
ren ER40475 wrace4
ren ER37536 outkid
ren ER36029 house
ren ER41034 smsa  
ren ER36007 fchg
ren ER36003  state
ren ER41069 weight 
ren ER36109  empst1
ren ER36110 empst2
ren ER36111 empst3
ren ER36367  wempst1
ren ER36368  wempst2
ren ER36369  wempst3
ren ER36672  foodstamps
ren ER41037  educ
ren ER41038  weduc
ren ER40873 weeks_emp
ren ER40884 weeks_empw
ren ER36165  exp_job_years
ren ER36166 exp_job_months
ren ER36167 exp_job_weeks
ren ER36311 unempl 
ren ER36304 layoff 
ren ER40958  tr_unem
ren ER40980  tr_unemw
ren ER40960 tr_comp
ren ER40479 sp_veteran
ren ER40572  veteran
ren ER36132 occupation


ren ER40959 tr_unem_acc
ren ER40981 tr_unemw_acc
ren ER40961 tr_comp_acc


ren ER38206 disab
ren ER38207 twdisab 
ren ER38208 hmdisab

ren ER39303 sp_disab
ren ER39304 sp_twdisab
ren ER39305 sp_hmdisab

ren ER40616  workyears
ren ER40617  workyears_full
ren ER40524 spworkyears_full 
ren ER40523 spworkyears


replace weeks_emp =. if weeks_emp == 98 | weeks_emp ==99
replace exp_job_years =. if exp_job_years == 98 | exp_job_years ==99
replace exp_job_months =. if exp_job_months == 98 | exp_job_months ==99
replace exp_job_weeks =. if exp_job_weeks == 98 | exp_job_weeks ==99

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace layoff =. if layoff == 8 | layoff == 9
replace layoff =0 if layoff == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99

///HEALTH MEASURES ///
ren ER38267 in_hosp
ren ER38268 days_in_hosp

ren ER38237 arthritis
ren ER38225 asthma
ren ER38221 bloodp
ren ER38249 cancer
ren ER38233 diabetes  
ren ER38256 emotion
ren ER38213 heart_att
ren ER38217 heart_dis
ren ER38229 lung_dis
ren ER38209 stroke    

ren ER38291 trouble_meals
ren ER38292 trouble_meals_health
ren ER38293 trouble_shop
ren ER38294 trouble_shop_health
ren ER38295 trouble_money
ren ER38296 trouble_money_health
ren ER38297 trouble_phone
ren ER38298 trouble_phone_health
ren ER38299 trouble_heavywork
ren ER38300 trouble_heavywork_health
ren ER38301 trouble_lightwork
ren ER38302 trouble_lightwork_health


/// ASSETS ///
***********
* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren S803  bus_ass
ren S803A  bus_ass_acc
gen bus_debt =.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S805  cash
ren S805A  cash_acc  

ren S809  real_estate
ren S809A  real_estate_acc
gen real_estate_debt=.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S811  stocks
ren S811A  stocks_acc

ren S813  vehicles
ren S813A  vehicles_acc

ren S815  other_ass
ren S815A  other_ass_acc

ren S819 ira
ren S819A ira_acc

ren S807  other_debt
ren S807A  other_debt_acc // Credit card charges, student loans, medical or legal bills, or loans from relatives?

/* ^^^^ Note "other debt" to be calculated manually after 2009*/

ren S820  home_equity
ren S820A  home_equity_acc

ren S817  w_with_h
ren S817A  w_with_h_acc


/* ^^^^ Constructed wealth variable, including equity. This imputed variable is constructed as
sum of values of seven asset types (ER46938, ER46942, ER46950, ER46954, ER46956, ER46960,
ER46964) net of debt value (ER46946) plus value of home equity (ER46966). All missing data
were assigned. */

ren ER37761 pension_plan
replace pension_plan=. if pension_plan>999999997

/* No data on wife pension pre 2011 */ 

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=36676
        while `i' < 36688  {
            replace ER`i'=. if ER`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(ER36676-ER36687)
replace fstmp=ER36673              if ER36674 ==6 
replace fstmp=ER36673*nm           if ER36674 ==5 
replace fstmp=ER36673*((26/12)*nm) if ER36674 ==4  
replace fstmp=ER36673*((52/12)*nm) if ER36674 ==3
replace fstmp=.					 if ER36673>999997 | ER36674 >6 | ER36674 ==1 
drop nm

ren ER36706 fdhm1
ren ER36707 fdhmpER1
ren ER36716 fdhm2
ren ER36717  fdhmpER2
ren ER36710  fddel1
ren ER36720  fddel2
ren ER36711 fddelpER1
ren ER36721 fddelpER2
ren ER36713 fout1
ren ER36723 fout2
ren ER36714  foutpER1
ren ER36724  foutpER2
* Note that these are taken from the imputed values, _acc variables denote imputed entries
ren ER41027A2  food_home
ren ER41027A3 food_out
ren ER41027A4 food_deliv

ren ER41027A6 mortgage  
ren ER36044 mortgage_raw1
ren ER36056 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER41027A7 rent
ren ER36065 rent_raw
ren ER36066 rent_per
gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER41027A8 prop_tax 
ren ER36036 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER41027A9 homeinsure
ren ER36038 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren ER41027B1 utilities
ren ER36083  gas_raw
ren ER36085  electric_raw
ren ER36089  water_raw
ren ER36095   otheru_raw
gen utilities_acc = 0
replace utilities_acc = 1 if gas_raw >= 9999999 | electric_raw >= 9999999 | water_raw >= 9999999 | otheru_raw >= 9999999 


rename ER36086  electric_per
rename ER36084 gas_per
rename ER36090 water_per
rename ER36096 miscutils_per



ren ER41027B6 telecom
ren ER36091 telecom_raw
ren ER36092 telecom_per
gen telecom_acc = 0
replace telecom_acc = 1 if telecom_raw>=9998

ren ER36813  carinsper

ren ER41027B8 vehicle_loan
ren ER36747 vehicle_loan_raw1 
ren ER36775  vehicle_loan_raw2
ren ER36803 vehicle_loan_raw3
gen vehicle_loan_acc = 0
replace vehicle_loan_acc = 1 if vehicle_loan_raw1 >=999998 | vehicle_loan_raw2 >=999998 | vehicle_loan_raw3 >=999998 

ren ER41027B9 vehicle_down
ren ER36744 vehicle_down_raw1
ren ER36772 vehicle_down_raw2
ren ER36800 vehicle_down_raw3
gen vehicle_down_acc = 0
replace vehicle_down_acc = 1 if vehicle_down_raw1 >= 999998 | vehicle_down_raw2 >= 999998 | vehicle_down_raw3 >= 999998 

ren ER41027C1 vehicle_lease
ren ER36751 vehicle_lease_raw1
ren ER36779 vehicle_lease_raw2
ren ER36807  vehicle_lease_raw3
gen vehicle_lease_acc = 0
replace vehicle_lease_acc = 1 if vehicle_lease_raw1 >=999998 | vehicle_lease_raw2 >=999998 |vehicle_lease_raw3 >=999998 

ren ER41027C2 auto_ins
ren ER36812 auto_ins_raw
gen auto_ins_acc = 0
replace auto_ins_acc = 1 if auto_ins_raw >= 999998

ren ER41027C3 other_vehicle
ren ER36815 other_vehicle_raw
gen other_vehicle_acc = 0
replace other_vehicle_acc  = 1 if other_vehicle_raw >=999998

ren ER41027C5 gasoline
ren ER36817 gasoline_raw
gen gasoline_acc = 0
replace gasoline_acc = 1 if gasoline_raw >= 99998

ren ER41027C4 auto_repair
ren ER36816 auto_repair_raw
gen auto_repair_acc = 0
replace auto_repair_acc = 1 if auto_repair_raw >=99998

ren ER41027C6 parking
ren ER36818 parking_raw
gen parking_acc = 0
replace parking_acc = 1 if parking_raw >= 99998 

ren ER41027C7 bus_train
ren ER36819 bus_train_raw
gen bus_train_acc = 0
replace bus_train_acc = 1 if bus_train_raw >= 99998

ren ER41027C8 taxi
ren ER36820 taxi_raw
gen taxi_acc = 0
replace taxi_acc = 1 if taxi_raw >= 99998

ren ER41027C9 other_trans
ren ER36821 other_trans_raw
gen other_trans_acc = 0
replace other_trans_acc = 1 if other_trans_raw >=99998

ren ER41027D1 education
ren ER36823 education_raw1
ren ER36825 education_raw2
gen education_acc = 0
replace education_acc = 1 if education_raw1 >= 999998 | education_raw2 >= 999998

ren ER41027D2 childcare
ren ER36633 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

ren ER41027D7 health_ins

ren ER41027D8 home_repair
ren ER36826 home_repair_raw
gen home_repair_acc = 0
replace home_repair_acc = 1 if home_repair_raw >= 999998

ren ER41027D9 home_furnish
ren ER36831 home_furnish_raw
gen home_furnish_acc = 0
replace home_furnish_acc = 1 if home_furnish_raw >= 999998

ren ER41027E1 clothing
ren ER36836 clothing_raw
gen clothing_acc = 0
replace clothing_acc = 1 if clothing_raw >= 999998

ren ER41027E2 trips
ren ER36841 trips_raw
gen trips_acc = 0
replace trips_acc = 1 if trips_raw >= 999998

ren ER41027E3 other_rec
ren ER36846 other_rec_raw
gen other_rec_acc = 0
replace other_rec_acc = 1 if other_rec >= 999998


/// INCOME ///

ren ER41027 y
gen truncy=y<1
replace y=1 if y<1

ren ER40943 tyhw
ren ER40992 trhw
ren ER40999 tyoth
ren ER41020 troth
ren ER41021 soc_sec
ren ER41023 soc_secw
ren ER41025 soc_secyoth



* Asset Income *

ren ER40922 renty
ren ER40923 renty_acc
ren ER40935 rentyw
ren ER40936 rentyw_acc

ren ER40924 divi
ren ER40925 divi_acc
ren ER40937 diviw
ren ER40938 diviw_acc

ren ER40926 interest
ren ER40927 interest_acc
ren ER40939 interestw
ren ER40940 interestw_acc

ren ER40928 trustinc
ren ER40929  trustinc_acc
ren ER40941 trustincw
ren ER40942 trustincw_acc

ren ER40901 businc
ren ER40931 busincw

* Generate labor and asset part of farm income *
gen farmlabor=0
replace farmlabor=0.5*ER40898 			if ER40898>0
replace farmlabor=. 					if ER40898>9999997

gen farmasset=0
replace farmasset=0.5*ER40898 			if ER40898>0
replace farmasset=ER40898 				if ER40898<0
replace farmasset=. 					if ER40898>9999997

* Hours Worked *
ren ER40876 hours
ren ER40887 hourw

* Income *
ren ER40921 wages
ren ER40933 wagesw
ren ER40900 y_business
ren ER40930 yw_business

egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)

ren ER40410  hinsurance
ren ER40414  nurse
ren ER40420 doctor
ren ER40426 prescription
ren ER40432 totalhealth
ren ER36134 self

/// Prep variables to save ///
ren ER40915 gardasset
ren ER36982 roomerinc
ren ER36983  roomerper

ren ER37545  chsupp_paid
/// Prep variables to save ///

#delimit;

keep 

moved
id fsize age sex agew kids marit race* wrace* *disab* *disab* sp_veteran veteran occupation
outkid house smsa fchg state weight foodstamps *educ newborn

gardasset roomerinc roomerper chsupp

bus_ass* bus_debt* cash* real_estate* real_estate_debt* stocks* vehicles* other_ass*
ira* other_debt* home_equity* w_with_h* self*

fstmp food_home fd* fo* food_out food_deliv mortgage rent prop_tax homeinsure utilities telecom
vehicle_loan vehicle_down vehicle_lease auto_ins other_vehicle gasoline auto_repair
parking bus_train taxi other_trans education* childcare doctor prescription
health_ins home_repair home_furnish clothing trips other_rec hinsurance nurse 

y truncy tyhw trhw tyoth troth soc_sec soc_secw soc_secyoth
farmlabor farmasset hours* wages* y_business yw_business ly wly weeks_emp* hourw
exp_job_years exp_job_months exp_job_weeks empst* wempst* unempl layoff
rent* divi* interest* trust* bus* *per *raw  tr_comp tr_unemw tr_unem *workyears*
arthritis asthma bloodp cancer diabetes emotion heart_att heart_dis lung_dis stroke trouble*

; 

#delimit cr


gen year=2007

save "$temp\HH_2007.dta", replace

********************************************************************************************
*2005
********************************************************************************************

use "$data\2005_H", clear
rename ER25002 S701
merge 1:1 S701 using "$data\2005_W.dta"
rename S701 ER25002 

ren ER25098 moved
ren ER25002 id
ren ER25016 fsize
ren ER25017 age
ren ER25018 sex
ren ER25019 agew
ren ER25020 kids
ren ER25021 newborn
ren ER25023 marit
ren ER27393 race1
ren ER27394 race2
ren ER27297 wrace1
ren ER27298 wrace2
ren ER26518 outkid
ren ER25029 house
ren ER28044 smsa  
ren ER25007 fchg
ren ER25003  state
ren ER28078 weight 
ren ER25104 empst1
ren ER25362 wempst1
ren ER28047  educ
*1991 onward:
ren ER28048  weduc

*Dropping weeks of work, years, months, weeks of experience
ren ER25306 unempl 
ren ER25299 layoff 
ren ER27968  tr_unem
ren ER26166 tr_unemraw
ren ER26167 tr_unemper
ren ER27990  tr_unemw
ren ER26285 tr_unemwraw
ren ER26286 tr_unemwper
ren ER27970 tr_comp
ren ER26182 tr_compraw
ren ER26183 tr_compper
ren ER27304 sp_veteran
ren ER27400  veteran
ren ER25127 occupation

ren ER27969 tr_unem_acc
ren ER27991 tr_unemw_acc
ren ER27971 tr_comp_acc


ren ER26995 disab
ren ER26996 twdisab 
ren ER26997 hmdisab

ren ER27118 sp_disab
ren ER27119 sp_twdisab
ren ER27120 sp_hmdisab

ren ER27444  workyears
ren ER27445  workyears_full
ren ER27349 spworkyears_full 
ren ER27348  spworkyears


replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace layoff =. if layoff == 8 | layoff == 9
replace layoff =0 if layoff == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99

///HEALTH MEASURES ///
ren ER27056 in_hosp
ren ER27057 days_in_hosp

ren ER27026 arthritis
ren ER27014 asthma
ren ER27010 bloodp
ren ER27038 cancer
ren ER27022 diabetes  
ren ER27045 emotion
ren ER27002 heart_att
ren ER27006 heart_dis
ren ER27018 lung_dis
ren ER26998 stroke    

ren ER27080 trouble_meals
ren ER27081 trouble_meals_health
ren ER27082 trouble_shop
ren ER27083 trouble_shop_health
ren ER27084 trouble_money
ren ER27085 trouble_money_health
ren ER27086 trouble_phone
ren ER27087 trouble_phone_health
ren ER27088 trouble_heavywork
ren ER27089 trouble_heavywork_health
ren ER27090 trouble_lightwork
ren ER27091 trouble_lightwork_health


/// ASSETS ///

***********
* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren S703  bus_ass
ren S703A   bus_ass_acc
gen bus_debt =.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S705  cash
ren S705A  cash_acc  

ren S709  real_estate
ren S709A  real_estate_acc
gen real_estate_debt=.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S711  stocks
ren S711A  stocks_acc

ren S713  vehicles
ren S713A   vehicles_acc

ren S715  other_ass
ren S715A  other_ass_acc

ren S719 ira
ren S719A ira_acc

ren S707  other_debt
ren S707A  other_debt_acc // Credit card charges, student loans, medical or legal bills, or loans from relatives?

/* ^^^^ Note "other debt" to be calculated manually after 2009*/

ren S720  home_equity
ren S720A   home_equity_acc

ren S717  w_with_h
ren S717A   w_with_h_acc


/* ^^^^ Constructed wealth variable, including equity. This imputed variable is constructed as
sum of values of seven asset types (ER46938, ER46942, ER46950, ER46954, ER46956, ER46960,
ER46964) net of debt value (ER46946) plus value of home equity (ER46966). All missing data
were assigned. */

ren ER26725  pension_plan
replace pension_plan=. if pension_plan>999999997

/* No data on wife pension pre 2011 */ 

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=25658 
        while `i' <= 25669 {
            replace ER`i'=. if ER`i'>1
        	local i=`i'+1
       }
end
qui doit

egen nm=rsum(ER25658-ER25669)


replace fstmp=ER25655              if ER25656 ==6 
replace fstmp=ER25655*nm           if ER25656 ==5 
replace fstmp=ER25655*((26/12)*nm) if ER25656 ==4  
replace fstmp=ER25655*((52/12)*nm) if ER25656 ==3
replace fstmp=.					 if ER25655>999997 | ER25656 >6 | ER25656 ==1 
drop nm

*ren [86]V12779 [87]V13881 [88]V14896 [89]V16396 [90]V17812 [91]V19112 [92]V20412 [93]V21728 [01]ER18389 [03]ER21655 [05]ER25657 fstmp_imp
*replace fstmp = . if fstmp_imp == 1

*below is spending on food at home if food stamps
ren ER25688 fdhm1
ren ER25690 fdhm1_imp

ren ER25689 fdhmpER1
*below is spending on food at home, if not food stamps
ren ER25698 fdhm2
ren ER25699 fdhmpER2
*below is cost of food delivered through food stamps
ren ER25692 fddel1
ren ER25693 fddelpER1
*below is cost of food delivered without food stamps
ren ER25702  fddel2
ren ER25703 fddelpER2
*below is cost of eating out, if food stamps
ren ER25695 fout1
ren ER25696  foutpER1
*below is cost of eating out, if not food stamps
ren ER25705 fout2
ren ER25706  foutpER2
* Note that these are taken from the imputed values, _acc variables denote imputed entries
*they combine the food stamp + no food stamp measures
*note this is only done for us 1999+, even though structure begins in 1994.
ren ER28037A2  food_home
ren ER28037A3 food_out
ren ER28037A4 food_deliv

ren ER28037A6 mortgage  
ren ER25044 mortgage_raw1
ren ER25055 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER28037A7 rent
ren ER25063 rent_raw
ren ER25064 rent_per
gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER28037A8 prop_tax 
ren ER25036 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER28037A9 homeinsure
ren ER25038 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren ER28037B1 utilities
ren ER25080  gas_raw
ren ER25082  electric_raw
ren ER25084  water_raw
ren ER25090   otheru_raw
gen utilities_acc = 0
replace utilities_acc = 1 if gas_raw >= 9999999 | electric_raw >= 9999999 | water_raw >= 9999999 | otheru_raw >= 9999999 


rename ER25083  electric_per
rename ER25081 gas_per
rename ER25085  water_per
rename ER25091 miscutils_per



ren ER28037B6  telecom
ren ER25086 telecom_raw
ren ER25087 telecom_per
gen telecom_acc = 0
replace telecom_acc = 1 if telecom_raw>=9998

ren ER25795  carinsper

ren ER28037B8 vehicle_loan
ren ER25729 vehicle_loan_raw1 
ren ER25757  vehicle_loan_raw2
ren ER25785  vehicle_loan_raw3
gen vehicle_loan_acc = 0
replace vehicle_loan_acc = 1 if vehicle_loan_raw1 >=999998 | vehicle_loan_raw2 >=999998 | vehicle_loan_raw3 >=999998 

ren ER28037B9 vehicle_down
ren ER25726 vehicle_down_raw1
ren ER25754 vehicle_down_raw2
ren ER25782 vehicle_down_raw3
gen vehicle_down_acc = 0
replace vehicle_down_acc = 1 if vehicle_down_raw1 >= 999998 | vehicle_down_raw2 >= 999998 | vehicle_down_raw3 >= 999998 

ren ER28037C1 vehicle_lease
ren ER25733 vehicle_lease_raw1
ren ER25761 vehicle_lease_raw2
ren ER25789  vehicle_lease_raw3
gen vehicle_lease_acc = 0
replace vehicle_lease_acc = 1 if vehicle_lease_raw1 >=999998 | vehicle_lease_raw2 >=999998 |vehicle_lease_raw3 >=999998 

ren ER28037C2 auto_ins
ren ER25794 auto_ins_raw
gen auto_ins_acc = 0
replace auto_ins_acc = 1 if auto_ins_raw >= 999998

ren ER28037C3 other_vehicle
ren ER25797 other_vehicle_raw
gen other_vehicle_acc = 0
replace other_vehicle_acc  = 1 if other_vehicle_raw >=999998

ren ER28037C5 gasoline
ren ER25799 gasoline_raw
gen gasoline_acc = 0
replace gasoline_acc = 1 if gasoline_raw >= 99998

ren ER28037C4 auto_repair
ren ER25798 auto_repair_raw
gen auto_repair_acc = 0
replace auto_repair_acc = 1 if auto_repair_raw >=99998

ren ER28037C6 parking
ren ER25800 parking_raw
gen parking_acc = 0
replace parking_acc = 1 if parking_raw >= 99998 

ren ER28037C7 bus_train
ren ER25801  bus_train_raw
gen bus_train_acc = 0
replace bus_train_acc = 1 if bus_train_raw >= 99998

ren ER28037C8 taxi
ren ER25802  taxi_raw
gen taxi_acc = 0
replace taxi_acc = 1 if taxi_raw >= 99998

ren ER28037C9 other_trans
ren ER25803 other_trans_raw
gen other_trans_acc = 0
replace other_trans_acc = 1 if other_trans_raw >=99998

ren ER28037D1 education
ren ER25805 education_raw1
ren ER25807 education_raw2
gen education_acc = 0
replace education_acc = 1 if education_raw1 >= 999998 | education_raw2 >= 999998

ren ER28037D2 childcare
ren ER25628 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

ren ER28037D7 health_ins

ren ER28037D8 home_repair
ren ER25808 home_repair_raw
gen home_repair_acc = 0
replace home_repair_acc = 1 if home_repair_raw >= 999998

ren ER28037D9 home_furnish
ren ER25813 home_furnish_raw
gen home_furnish_acc = 0
replace home_furnish_acc = 1 if home_furnish_raw >= 999998

ren ER28037E1 clothing
ren ER25818 clothing_raw
gen clothing_acc = 0
replace clothing_acc = 1 if clothing_raw >= 999998

ren ER28037E2 trips
ren ER25823 trips_raw
gen trips_acc = 0
replace trips_acc = 1 if trips_raw >= 999998

ren ER28037E3 other_rec
ren ER25828 other_rec_raw
gen other_rec_acc = 0
replace other_rec_acc = 1 if other_rec >= 999998


/// INCOME ///

ren ER28037 y
gen truncy=y<1
replace y=1 if y<1

ren ER27953 tyhw
ren ER28002 trhw
ren ER28009 tyoth
ren ER28030 troth
ren ER28031 soc_sec
ren ER28033 soc_secw
ren ER28035 soc_secyoth



* Asset Income *
*******************************
*MAP PERIOD-SPECIFIC AMOUNTS OF RENT INCOME INTO ANNAULS, WITH MONTHS OF RECEIPT
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
*fix the month vars so that 9 is missing
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end

ren ER27932 renty
ren ER27933 renty_acc
doit, var(renty2) varamt(ER25984) varper(ER25985) varstart(ER25988) varend(ER25999)

ren ER27945 rentyw
ren ER27946 rentyw_acc
doit, var(rentyw2) varamt(ER26317) varper(ER26318) varstart(ER26321) varend(ER26332)

ren ER27934 divi
ren ER27935 divi_acc
doit, var(divi2) varamt(ER26001) varper(ER26002) varstart(ER26005) varend(ER26016)


ren ER27947 diviw
ren ER27948 diviw_acc
doit, var(diviw2) varamt(ER26334) varper(ER26335) varstart(ER26338) varend(ER26349)

ren ER27936 interest
ren ER27937 interest_acc
doit, var(interest2) varamt(ER26018) varper(ER26019) varstart(ER26022) varend(ER26033)

ren ER27949 interestw
ren ER27950 interestw_acc
doit, var(interestw2) varamt(ER26351) varper(ER26352) varstart(ER26355) varend(ER26366)

ren ER27938 trustinc
ren ER27939  trustinc_acc 
doit, var(trustinc2) varamt(ER26035) varper(ER26036) varstart(ER26038) varend(ER26049)

ren ER27951 trustincw
ren ER27952 trustincw_acc
doit, var(trustincw2) varamt(ER26368) varper(ER26369) varstart(ER26371) varend(ER26382)
*******************************
program drop doit


ren ER27911 businc
ren ER27941 busincw

* Generate labor and asset part of farm income *
gen farmlabor=0
replace farmlabor=0.5*ER27908 			if ER27908>0
replace farmlabor=. 					if ER27908>9999997

gen farmasset=0
replace farmasset=0.5*ER27908 			if ER27908>0
replace farmasset=ER27908 				if ER27908<0
replace farmasset=. 					if ER27908>9999997

* Hours Worked *
ren ER27886 hours
ren ER27897 hourw

*Labor Income (no farm in 1994 and onward)*
ren ER27931 wages
*Labor Income (no farm in 1994 and onward)*
ren ER27943 wagesw
ren ER27910 y_business
ren ER27940 yw_business

*change these for 1993 and before
egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)

ren ER27238  hinsurance
ren ER27239   nurse
ren ER27245 doctor
ren ER27251 prescription
ren ER27257 totalhealth
ren ER25129 self

/// Prep variables to save ///
*garden market income
ren ER27925 gardasset

ren ER25964 roomerinc
ren ER25965  roomerper

ren ER26527  chsupp_paid
/// Prep variables to save ///

#delimit;

keep 

moved
id fsize age sex agew kids marit race* wrace* *disab* *disab* sp_veteran veteran occupation
outkid house smsa fchg state weight  *educ newborn

gardasset roomerinc roomerper chsupp

bus_ass* bus_debt* cash* real_estate* real_estate_debt* stocks* vehicles* other_ass*
ira* other_debt* home_equity* w_with_h* self*

fstmp food_home fd* fo* food_out food_deliv  rent prop_tax homeinsure utilities telecom
vehicle_loan vehicle_down vehicle_lease auto_ins other_vehicle gasoline auto_repair
parking bus_train taxi other_trans education* childcare doctor prescription
health_ins home_repair home_furnish clothing trips other_rec hinsurance nurse 

y truncy tyhw trhw tyoth troth soc_sec soc_secw soc_secyoth
farmlabor farmasset hours* wages* y_business yw_business ly wly  hourw
empst* wempst* unempl layoff
rent* divi* interest* trust* bus* *per *raw  tr_comp tr_unemw tr_unem *workyears*
mortgage* vehicle* education* pension_plan totalhealth *acc
arthritis asthma bloodp cancer diabetes emotion heart_att heart_dis lung_dis stroke trouble*

; 

#delimit cr


gen year=2005

save "$temp\HH_2005.dta", replace


********************************************************************************************
* 2003
********************************************************************************************

use "$data\2003_H", clear

rename ER21002 S601
merge 1:1 S601 using "$data\2003_W.dta"
rename S601 ER21002 

ren ER21002 id

drop _merge

ren ER21117 moved
ren ER21016 fsize
ren ER21017 age
ren ER21018 sex
ren ER21019 agew
ren ER21020 kids
ren ER21021 newborn
ren ER21023 marit
ren ER23426 race1
ren ER23427 race2
ren ER23334 wrace1
ren ER23335 wrace2
ren ER22537 outkid
ren ER21043 house
ren ER24145 smsa  
ren ER21007 fchg
ren ER21003 state
ren ER24179 weight 
ren ER21123 empst1
ren ER21373 wempst1
ren ER24148 educ
ren ER24149 weduc

*Dropping weeks of work, years, months, weeks of experience
ren ER21317 unempl 
ren ER21310 layoff 
ren ER22185 tr_unemraw
ren ER22186 tr_unemper
ren ER22304 tr_unemwraw
ren ER22305 tr_unemwper
ren ER22201 tr_compraw
ren ER22202 tr_compper

ren ER22187 tr_unem_acc
ren ER22306 tr_unemw_acc
ren ER22203 tr_comp_acc
ren ER23341 sp_veteran
ren ER23433  veteran
ren ER21145 occupation



ren ER23014 disab
ren ER23015 twdisab 
ren ER23016 hmdisab

ren ER23141 sp_disab
ren ER23142 sp_twdisab
ren ER23143 sp_hmdisab

ren ER23476 workyears
ren ER23477 workyears_full
ren ER23385 spworkyears_full 
ren ER23384 spworkyears


replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace layoff =. if layoff == 8 | layoff == 9
replace layoff =0 if layoff == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99

///HEALTH MEASURES ///
ren ER23089 in_hosp
ren ER23090 days_in_hosp

ren ER23065 arthritis
ren ER23071 asthma
ren ER23023 bloodp
ren ER23035 cancer
ren ER23029 diabetes  
ren ER23059 emotion
ren ER23047 heart_att
ren ER23053 heart_dis
ren ER23041 lung_dis
ren ER23017 stroke    

ren ER23106 trouble_meals
ren ER23107 trouble_meals_health
ren ER23108 trouble_shop
ren ER23109 trouble_shop_health
ren ER23110 trouble_money
ren ER23111 trouble_money_health
ren ER23112 trouble_phone
ren ER23113 trouble_phone_health
ren ER23114 trouble_heavywork
ren ER23115 trouble_heavywork_health
ren ER23116 trouble_lightwork
ren ER23117 trouble_lightwork_health


/// ASSETS ///

***********
* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren S603   bus_ass
ren S603A  bus_ass_acc
gen bus_debt =.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S605 cash
ren S605A cash_acc  

ren S609 real_estate
ren S609A  real_estate_acc
gen real_estate_debt=.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S611 stocks
ren S611A stocks_acc

ren S613 vehicles
ren S613A vehicles_acc

ren S615 other_ass
ren S615A other_ass_acc

ren S619 ira
ren S619A ira_acc

ren S607 other_debt
ren S607A other_debt_acc // Credit card charges, student loans, medical or legal bills, or loans from relatives?

/* ^^^^ Note "other debt" to be calculated manually after 2009*/

ren S620 home_equity
ren S620A home_equity_acc

ren S617 w_with_h
ren S617A w_with_h_acc


/* ^^^^ Constructed wealth variable, including equity. This imputed variable is constructed as
sum of values of seven asset types (ER46938, ER46942, ER46950, ER46954, ER46956, ER46960,
ER46964) net of debt value (ER46946) plus value of home equity (ER46966). All missing data
were assigned. */

ren ER22744 pension_plan
replace pension_plan=. if pension_plan>999999997

/* No data on wife pension pre 2011 */ 

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=21656
        while `i' <= 21667  {
            replace ER`i'=. if ER`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(ER21656-ER21667)


replace fstmp=ER21653              if ER21654 ==6 
replace fstmp=ER21653*nm           if ER21654 ==5 
replace fstmp=ER21653*((26/12)*nm) if ER21654 ==4  
replace fstmp=ER21653*((52/12)*nm) if ER21654 ==3
replace fstmp=.					 if ER21653>999997 | ER21654 >6 | ER21654 ==1 
drop nm

*ren [86]V12779 [87]V13881 [88]V14896 [89]V16396 [90]V17812 [91]V19112 [92]V20412 [93]V21728 [01]ER18389 [03]ER21655 [05]ER25657 fstmp_imp
*replace fstmp = . if fstmp_imp == 1

*below is spending on food at home if food stamps
ren ER21686 fdhm1
ren ER21688 fdhm1_imp

ren ER21687 fdhmpER1
*below is spending on food at home, if not food stamps
ren ER21696 fdhm2
ren ER21697 fdhmpER2
*below is cost of food delivered through food stamps
ren ER21690 fddel1
ren ER21691 fddelpER1
*below is cost of food delivered without food stamps
ren ER21700 fddel2
ren ER21701 fddelpER2
*below is cost of eating out, if food stamps
ren ER21693 fout1
ren ER21694 foutpER1
*below is cost of eating out, if not food stamps
ren ER21703 fout2
ren ER21704 foutpER2
* Note that these are taken from the imputed values, _acc variables denote imputed entries
*they combine the 
*note this is only done for us 1999+, even though structure begins in 1994.
ren ER24138A2 food_home
ren ER24138A3 food_out
ren ER24138A4 food_deliv

ren ER24138A6 mortgage  
ren ER21053 mortgage_raw1
ren ER21064 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER24138A7 rent
ren ER21072 rent_raw
ren ER21073 rent_per
gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER24138A8 prop_tax 
ren ER21045 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER24138A9 homeinsure
ren ER21047 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren ER24138B1 utilities
ren ER21088 gas_raw
ren ER21086 electric_raw
ren ER21090 water_raw
ren ER21097 otheru_raw
gen utilities_acc = 0
replace utilities_acc = 1 if gas_raw >= 9999999 | electric_raw >= 9999999 | water_raw >= 9999999 | otheru_raw >= 9999999 


rename ER21087 electric_per
rename ER21089 gas_per
rename ER21091 water_per
rename ER21098 miscutils_per


ren ER21839 carinsper

ren ER24138B7 vehicle_loan
ren ER21771 vehicle_loan_raw1 
ren ER21800 vehicle_loan_raw2
ren ER21829 vehicle_loan_raw3
gen vehicle_loan_acc = 0
replace vehicle_loan_acc = 1 if vehicle_loan_raw1 >=999998 | vehicle_loan_raw2 >=999998 | vehicle_loan_raw3 >=999998 

ren ER24138B8 vehicle_down
ren ER21768 vehicle_down_raw1
ren ER21797 vehicle_down_raw2
ren ER21826 vehicle_down_raw3
gen vehicle_down_acc = 0
replace vehicle_down_acc = 1 if vehicle_down_raw1 >= 999998 | vehicle_down_raw2 >= 999998 | vehicle_down_raw3 >= 999998 

ren ER24138B9 vehicle_lease
ren ER21775 vehicle_lease_raw1
ren ER21804 vehicle_lease_raw2
ren ER21833 vehicle_lease_raw3
gen vehicle_lease_acc = 0
replace vehicle_lease_acc = 1 if vehicle_lease_raw1 >=999998 | vehicle_lease_raw2 >=999998 |vehicle_lease_raw3 >=999998 

ren ER24138C1 auto_ins
ren ER21838 auto_ins_raw
gen auto_ins_acc = 0
replace auto_ins_acc = 1 if auto_ins_raw >= 999998

ren ER24138C2 other_vehicle
ren ER21841 other_vehicle_raw
gen other_vehicle_acc = 0
replace other_vehicle_acc  = 1 if other_vehicle_raw >=999998

ren ER24138C4 gasoline
ren ER21843 gasoline_raw
gen gasoline_acc = 0
replace gasoline_acc = 1 if gasoline_raw >= 99998

ren ER24138C3 auto_repair
ren ER21842 auto_repair_raw
gen auto_repair_acc = 0
replace auto_repair_acc = 1 if auto_repair_raw >=99998

ren ER24138C5 parking
ren ER21844 parking_raw
gen parking_acc = 0
replace parking_acc = 1 if parking_raw >= 99998 

ren ER24138C6 bus_train
ren ER21845 bus_train_raw
gen bus_train_acc = 0
replace bus_train_acc = 1 if bus_train_raw >= 99998

ren ER24138C7 taxi
ren ER21846 taxi_raw
gen taxi_acc = 0
replace taxi_acc = 1 if taxi_raw >= 99998

ren ER24138C8 other_trans
ren ER21847 other_trans_raw
gen other_trans_acc = 0
replace other_trans_acc = 1 if other_trans_raw >=99998

ren ER24138C9 education
ren ER21849 education_raw1
ren ER21851 education_raw2
gen education_acc = 0
replace education_acc = 1 if education_raw1 >= 999998 | education_raw2 >= 999998

ren ER24138D1 childcare
ren ER21628 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

ren ER24138D6 health_ins


/// INCOME ///

ren ER24099 y
gen truncy=y<1
replace y=1 if y<1

ren ER24100 tyhw
ren ER24101 trhw
ren ER24102 tyoth
ren ER24103 troth

ren ER24104 soc_sectot



* Asset Income *
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end

doit, var(renty2) varamt(ER22003) varper(ER22004) varstart(ER22007) varend(ER22018)
doit, var(rentyw2) varamt(ER22336) varper(ER22337) varstart(ER22340) varend(ER22351)
doit, var(divi2) varamt(ER22020) varper(ER22021) varstart(ER22024) varend(ER22035)
doit, var(diviw2) varamt(ER22353) varper(ER22354) varstart(ER22357) varend(ER22368)
doit, var(interest2) varamt(ER22037) varper(ER22038) varstart(ER22041) varend(ER22052)
doit, var(interestw2) varamt(ER22370) varper(ER22371) varstart(ER22374) varend(ER22385)
doit, var(trustinc2) varamt(ER22054) varper(ER22055) varstart(ER22057) varend(ER22068)
doit, var(trustincw2) varamt(ER22387) varper(ER22388) varstart(ER22390) varend(ER22401)



ren ER24110 businc
ren ER24112 busincw

* Generate labor and asset part of farm income *
gen farmlabor=0
replace farmlabor=0.5*ER24105 			if ER24105>0
replace farmlabor=. 					if ER24105>9999997

gen farmasset=0
replace farmasset=0.5*ER24105 			if ER24105>0
replace farmasset=ER24105 				if ER24105<0
replace farmasset=. 					if ER24105>9999997

* Hours Worked *
ren ER24080 hours
ren ER24091 hourw

*Labor Income (no farm in 1994 and onward)*
ren ER24116 wages
*Labor Income (no farm in 1994 and onward)*
ren ER24135 wagesw
ren ER24109 y_business
ren ER24111 yw_business

*change these for 1993 and before
egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)

ren ER23278 hinsurance
ren ER23279 nurse
ren ER23285 doctor
ren ER23291 prescription
ren ER23297 totalhealth
ren ER21147 self

/// Prep variables to save ///
*garden market income
ren ER24129 gardasset

ren ER21983 roomerinc
ren ER21984 roomerper

ren ER22546 chsupp_paid
/// Prep variables to save ///

#delimit;

keep 

moved
id fsize age sex agew kids marit race* wrace* *disab* *disab* sp_veteran veteran occupation
outkid house smsa fchg state weight  *educ newborn

gardasset roomerinc roomerper chsupp

bus_ass* bus_debt* cash* real_estate* real_estate_debt* stocks* vehicles* other_ass*
ira* other_debt* home_equity* w_with_h* self*

fstmp food_home fd* fo* food_out food_deliv mortgage rent prop_tax homeinsure utilities 
mortgage* vehicle* education* pension_plan totalhealth
vehicle_loan vehicle_down vehicle_lease auto_ins other_vehicle gasoline auto_repair
parking bus_train taxi other_trans education childcare doctor prescription
health_ins    hinsurance nurse 

y truncy tyhw trhw tyoth troth soc_sec* 
farmlabor farmasset hours* wages* y_business yw_business ly wly hourw
 empst* wempst* unempl layoff
rent* divi* interest* trust* bus* *per *raw tr_comp* tr_unem* *workyears* *acc
arthritis asthma bloodp cancer diabetes emotion heart_att heart_dis lung_dis stroke trouble*

; 

#delimit cr

gen year = 2003


save "$temp\HH_2003.dta", replace


********************************************************************************************
* 2001
********************************************************************************************

use "$data\2001_H", clear
rename ER17002 S501
merge 1:1 S501 using "$data\2001_W.dta"
rename S501 ER17002
drop _merge



ren ER17088 moved
ren ER17002 id
ren ER17012 fsize
ren ER17013 age
ren ER17014 sex
ren ER17015 agew
ren ER17016 kids
ren ER17017 newborn
ren ER17024 marit
ren ER19989 race1
ren ER19990 race2
ren ER19897 wrace1
ren ER19898 wrace2
ren ER19172 outkid
ren ER17044 house
ren ER20378 smsa  
ren ER17007 fchg
ren ER17004 state
ren ER20394 weight 
ren ER17216 empst1
ren ER17786 wempst1
ren ER20457 educ
ren ER20458 weduc

*Dropping weeks of work, years, months, weeks of experience
ren ER17353  unemplayoff
ren ER18815 tr_unemraw
ren ER18816 tr_unemper
ren ER18934 tr_unemwraw
ren ER18935 tr_unemwper
ren ER18831 tr_compraw
ren ER18832 tr_compper
ren ER19904 sp_veteran
ren ER19996 veteran
ren ER17226 occupation

ren ER18817 tr_unem_acc
ren ER18936 tr_unemw_acc
ren ER18833 tr_comp_acc


ren ER19614 disab
ren ER19615 twdisab 
ren ER19616 hmdisab

ren ER19722 sp_disab
ren ER19723 sp_twdisab
ren ER19724 sp_hmdisab

ren ER20040 workyears
ren ER20041 workyears_full
ren ER19948 spworkyears_full 
ren ER19947 spworkyears

replace unemplayoff =. if unemplayoff == 8 | unemplayoff == 9
replace unemplayoff =0 if unemplayoff == 5


replace educ =. if educ == 99
replace weduc =. if weduc == 99

///HEALTH MEASURES ///
ren ER19665 arthritis
ren ER19671 asthma
ren ER19623 bloodp
ren ER19635 cancer
ren ER19629 diabetes  
ren ER19659 emotion
ren ER19647 heart_att
ren ER19653 heart_dis
ren ER19641 lung_dis
ren ER19617 stroke    

/// ASSETS ///

***********
* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren S503 bus_ass
ren S503A bus_ass_acc
gen bus_debt =.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S505 cash
ren S505A cash_acc  

ren S509 real_estate
ren S509A real_estate_acc
gen real_estate_debt=.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S511 stocks
ren S511A stocks_acc

ren S513 vehicles
ren S513A vehicles_acc

ren S515 other_ass
ren S515A other_ass_acc

ren S519 ira
ren S519A ira_acc

ren S507 other_debt
ren S507A other_debt_acc // Credit card charges, student loans, medical or legal bills, or loans from relatives?

/* ^^^^ Note "other debt" to be calculated manually after 2009*/

ren S520 home_equity
ren S520A home_equity_acc

ren S517 w_with_h
ren S517A w_with_h_acc


/* ^^^^ Constructed wealth variable, including equity. This imputed variable is constructed as
sum of values of seven asset types (ER46938, ER46942, ER46950, ER46954, ER46956, ER46960,
ER46964) net of debt value (ER46946) plus value of home equity (ER46966). All missing data
were assigned. */

ren ER19349 pension_plan
replace pension_plan=. if pension_plan>999999997

/* No data on wife pension pre 2011 */ 

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=18390 
        while `i' <= 18401  {
            replace ER`i'=. if ER`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(ER18390-ER18401)

*below are food stamps in average reference period

replace fstmp=ER18387              if ER18388 ==6 
replace fstmp=ER18387*nm           if ER18388 ==5 
replace fstmp=ER18387*((26/12)*nm) if ER18388 ==4  
replace fstmp=ER18387*((52/12)*nm) if ER18388 ==3
replace fstmp=.					 if ER18387>999997 | ER18388 >6 | ER18388 ==1 
drop nm

*ren [86]V12779 [87]V13881 [88]V14896 [89]V16396 [90]V17812 [91]V19112 [92]V20412 [93]V21728 [01]ER18389 [03]ER21655 [05]ER25657 fstmp_imp
*replace fstmp = . if fstmp_imp == 1

*below is spending on food at home if food stamps
ren ER18421 fdhm1
ren ER18423 fdhm1_imp

ren ER18422 fdhmpER1
*below is spending on food at home, if not food stamps
ren ER18431 fdhm2
ren ER18432 fdhmpER2
*below is cost of food delivered through food stamps
ren ER18425 fddel1
ren ER18426 fddelpER1
*below is cost of food delivered without food stamps
ren ER18435 fddel2
ren ER18436 fddelpER2
*below is cost of eating out, if food stamps
ren ER18428 fout1
ren ER18429 foutpER1
*below is cost of eating out, if not food stamps
ren ER18438 fout2
ren ER18439 foutpER2

* Note that these are taken from the imputed values, _acc variables denote imputed entries
*they combine the 
*note this is only done for us 1999+, even though structure begins in 1994.
ren ER20456A2 food_home
ren ER20456A3 food_out
ren ER20456A4 food_deliv

ren ER20456A6 mortgage  
ren ER17054 mortgage_raw1
ren ER17065 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER20456A7 rent
ren ER17074 rent_raw
ren ER17075 rent_per
gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER20456A8 prop_tax 
ren ER17046 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER20456A9 homeinsure
ren ER17048 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren ER20456B1 utilities
ren ER17099 gas_raw
ren ER17097 electric_raw
ren ER17101 water_raw
ren ER17108 otheru_raw
gen utilities_acc = 0
replace utilities_acc = 1 if gas_raw >= 9999999 | electric_raw >= 9999999 | water_raw >= 9999999 | otheru_raw >= 9999999 


rename ER17098 electric_per
rename ER17100 gas_per
rename ER17102 water_per
rename ER17109 miscutils_per


ren ER17203 carinsper

ren ER20456B7 vehicle_loan
ren ER17132 vehicle_loan_raw1 
ren ER17162 vehicle_loan_raw2
ren ER17192 vehicle_loan_raw3
gen vehicle_loan_acc = 0
replace vehicle_loan_acc = 1 if vehicle_loan_raw1 >=999998 | vehicle_loan_raw2 >=999998 | vehicle_loan_raw3 >=999998 

ren ER20456B8 vehicle_down
ren ER17129 vehicle_down_raw1
ren ER17159 vehicle_down_raw2
ren ER17189 vehicle_down_raw3
gen vehicle_down_acc = 0
replace vehicle_down_acc = 1 if vehicle_down_raw1 >= 999998 | vehicle_down_raw2 >= 999998 | vehicle_down_raw3 >= 999998 

ren ER20456B9 vehicle_lease
ren ER17137 vehicle_lease_raw1
ren ER17167 vehicle_lease_raw2
ren ER17197 vehicle_lease_raw3
gen vehicle_lease_acc = 0
replace vehicle_lease_acc = 1 if vehicle_lease_raw1 >=999998 | vehicle_lease_raw2 >=999998 |vehicle_lease_raw3 >=999998 

ren ER20456C1 auto_ins
ren ER17202 auto_ins_raw
gen auto_ins_acc = 0
replace auto_ins_acc = 1 if auto_ins_raw >= 999998

ren ER20456C2 other_vehicle
ren ER17205 other_vehicle_raw
gen other_vehicle_acc = 0
replace other_vehicle_acc  = 1 if other_vehicle_raw >=999998

ren ER20456C4 gasoline
ren ER17207 gasoline_raw
gen gasoline_acc = 0
replace gasoline_acc = 1 if gasoline_raw >= 99998

ren ER20456C3 auto_repair
ren ER17206 auto_repair_raw
gen auto_repair_acc = 0
replace auto_repair_acc = 1 if auto_repair_raw >=99998

ren ER20456C5 parking
ren ER17208 parking_raw
gen parking_acc = 0
replace parking_acc = 1 if parking_raw >= 99998 

ren ER20456C6 bus_train
ren ER17209 bus_train_raw
gen bus_train_acc = 0
replace bus_train_acc = 1 if bus_train_raw >= 99998

ren ER20456C7 taxi
ren ER17210 taxi_raw
gen taxi_acc = 0
replace taxi_acc = 1 if taxi_raw >= 99998

ren ER20456C8 other_trans
ren ER17211 other_trans_raw
gen other_trans_acc = 0
replace other_trans_acc = 1 if other_trans_raw >=99998

ren ER20456C9 education
ren ER17213 education_raw1
ren ER17215 education_raw2
gen education_acc = 0
replace education_acc = 1 if education_raw1 >= 999998 | education_raw2 >= 999998

ren ER20456D1 childcare
ren ER18362 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

ren ER20456D6 health_ins


/// INCOME ///

ren ER20456 y
gen truncy=y<1
replace y=1 if y<1

ren ER20449 tyhw
ren ER20450 trhw
ren ER20453 tyoth
ren ER20454 troth
ren ER20455 soc_sectot

* Asset Income *
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end

doit, var(renty2) varamt(ER18634) varper(ER18635) varstart(ER18637) varend(ER18648)
doit, var(divi2) varamt(ER18650) varper(ER18651) varstart(ER18653) varend(ER18664)
doit, var(diviw2) varamt(ER18966) varper(ER18967) varstart(ER18969) varend(ER18980)
doit, var(interest2) varamt(ER18666) varper(ER18667) varstart(ER18669) varend(ER18680)
doit, var(interestw2) varamt(ER18982) varper(ER18983) varstart(ER18985) varend(ER18996)
doit, var(trustinc2) varamt(ER18682) varper(ER18683) varstart(ER18685) varend(ER18696)
doit, var(trustincw2) varamt(ER18998) varper(ER18999) varstart(ER19001) varend(ER19012)


ren ER20423 businc
ren ER20445 busincw

* Generate labor and asset part of farm income *
*income:
gen farmlabor=0
replace farmlabor=0.5*ER20420 			if ER20420>0
replace farmlabor=. 					if ER20420>9999997

gen farmasset=0
replace farmasset=0.5*ER20420 			if ER20420>0
replace farmasset=ER20420 				if ER20420<0
replace farmasset=. 					if ER20420>9999997

* Hours Worked *
ren ER20399 hours
ren ER20410 hourw

*Labor Income (no farm in 1994 and onward)*
ren ER20443 wages
*Labor Income (no farm in 1994 and onward)*
ren ER20447 wagesw
ren ER20422 y_business
ren ER20444 yw_business

*change these for 1993 and before
egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)

ren ER19841 hinsurance
ren ER19842 nurse
ren ER19848 doctor
ren ER19854 prescription
ren ER19860 totalhealth
ren ER17221 self

/// Prep variables to save ///
*garden market income
ren ER20437 gardasset

ren ER18614 roomerinc
ren ER18615 roomerper

ren ER19181 chsupp_paid
/// Prep variables to save ///

#delimit;

keep 

moved
id fsize age sex agew kids marit race* wrace* *disab* *disab* sp_veteran veteran occupation
outkid house smsa fchg state weight  *educ newborn

gardasset roomerinc roomerper chsupp

bus_ass* bus_debt* cash* real_estate* real_estate_debt* stocks* vehicles* other_ass*
ira* other_debt* home_equity* w_with_h* self*
mortgage* vehicle* education* pension_plan totalhealth

fstmp food_home fd* fo* food_out food_deliv mortgage rent prop_tax homeinsure utilities 
vehicle_loan vehicle_down vehicle_lease auto_ins other_vehicle gasoline auto_repair
parking bus_train taxi other_trans education childcare doctor prescription
health_ins   hinsurance nurse  

y truncy tyhw trhw tyoth troth soc_sec*
farmlabor farmasset hours* wages* y_business yw_business ly wly  hourw
empst* wempst* unemplayoff
rent* divi* interest* trust* bus* *per *raw tr_comp* tr_unem* *workyears* *acc

arthritis asthma bloodp cancer diabetes emotion heart_att heart_dis lung_dis stroke

; 

#delimit cr

gen year=2001


save "$temp\HH_2001.dta", replace




********************************************************************************************
* 1999
********************************************************************************************

use "$data\1999_H", clear
rename ER13002  S401
merge 1:1 S401 using "$data\1999_W.dta"
rename S401 ER13002 
drop _merge


ren ER13077 moved
ren ER13002 id
ren ER13009 fsize
ren ER13010 age
ren ER13011 sex
ren ER13012 agew
ren ER13013 kids
ren ER13014 newborn
ren ER13021 marit
ren ER15928 race1
ren ER15929 race2
ren ER15836 wrace1
ren ER15837 wrace2
ren ER14976 outkid
ren ER13041 house
ren ER16432 smsa  
ren ER13008A fchg
ren ER13004 state
ren ER16518 weight 
ren ER13205 empst1
ren ER13717 wempst1
*1991 onward:
ren ER16516 educ
*1991 onward:
ren ER16517 weduc

*Dropping weeks of work, years, months, weeks of experience
ren ER13330 unemplayoff
ren ER14649 tr_unemraw
ren ER14650 tr_unemper
ren ER14760 tr_unemwraw
ren ER14761 tr_unemwper
ren ER14664 tr_compraw
ren ER14665 tr_compper
ren ER15843 sp_veteran
ren ER15935 veteran
ren ER13215 occupation



///HEALTH MEASURES ///
ren ER15500 arthritis
ren ER15506 asthma
ren ER15458 bloodp
ren ER15470 cancer
ren ER15464 diabetes  
ren ER15494 emotion
ren ER15482 heart_att
ren ER15488 heart_dis
ren ER15476 lung_dis
ren ER15452 stroke    


ren ER15449 disab
ren ER15450 twdisab 
ren ER15451 hmdisab

ren ER15557 sp_disab
ren ER15558 sp_twdisab
ren ER15559 sp_hmdisab

ren ER15979 workyears
ren ER15980 workyears_full
ren ER15887 spworkyears_full 
ren ER15886 spworkyears


replace educ =. if educ == 99
replace weduc =. if weduc == 99

/// ASSETS ///

***********
* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren S403 bus_ass
ren S403A bus_ass_acc
gen bus_debt =.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S405  cash
ren S405A cash_acc  

ren S409 real_estate
ren S409A real_estate_acc
gen real_estate_debt=.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S411 stocks
ren S411A stocks_acc

ren S413 vehicles
ren S413A vehicles_acc

ren S415 other_ass
ren S415A other_ass_acc

ren S419 ira
ren S419A ira_acc

ren S407 other_debt
ren S407A other_debt_acc // Credit card charges, student loans, medical or legal bills, or loans from relatives?

/* ^^^^ Note "other debt" to be calculated manually after 2009*/

ren S420 home_equity
ren S420A home_equity_acc

ren S417 w_with_h
ren S417A w_with_h_acc


/* ^^^^ Constructed wealth variable, including equity. This imputed variable is constructed as
sum of values of seven asset types (ER46938, ER46942, ER46950, ER46954, ER46956, ER46960,
ER46964) net of debt value (ER46946) plus value of home equity (ER46966). All missing data
were assigned. */

ren ER15181 pension_plan
replace pension_plan=. if pension_plan>999999997

/* No data on wife pension pre 2011 */ 

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=14258 
        while `i' <= 14269 {
            replace ER`i'=. if ER`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(ER14258-ER14269)

*with ref period:
 
replace fstmp=ER14256              if ER14257 ==6 
replace fstmp=ER14256*nm           if ER14257 ==5 
replace fstmp=ER14256*((26/12)*nm) if ER14257 ==4  
replace fstmp=ER14256*((52/12)*nm) if ER14257 ==3
replace fstmp=.					 if ER14256>999997 | ER14257 >6 | ER14257 ==1 
drop nm

*ren [86]V12779 [87]V13881 [88]V14896 [89]V16396 [90]V17812 [91]V19112 [92]V20412 [93]V21728 [01]ER18389 [03]ER21655 [05]ER25657 fstmp_imp
*replace fstmp = . if fstmp_imp == 1

*below is spending on food at home if food stamps
ren ER14288 fdhm1

ren ER14289 fdhmpER1
*below is spending on food at home, if not food stamps
ren ER14295 fdhm2
ren ER14296 fdhmpER2
*below is cost of food delivered through food stamps
ren ER14291 fddel1
ren ER14292 fddelpER1
*below is cost of food delivered without food stamps
ren ER14298 fddel2
ren ER14299 fddelpER2
*below is cost of eating out, if food stamps
ren ER14293 fout1
ren ER14294 foutpER1
*below is cost of eating out, if not food stamps
ren ER14300 fout2
ren ER14301 foutpER2
* Note that these are taken from the imputed values, _acc variables denote imputed entries
*they combine the 
*note this is only done for us 1999+, even though structure begins in 1994.
ren ER16515A2 food_home
ren ER16515A3 food_out
ren ER16515A4 food_deliv

ren ER16515A6 mortgage  
ren ER13048 mortgage_raw1
ren ER13057 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER16515A7 rent
ren ER13065 rent_raw
ren ER13066 rent_per
gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER16515A8 prop_tax 
ren ER13042 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER16515A9 homeinsure
ren ER13043 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren ER16515B1 utilities
ren ER13088 gas_raw
ren ER13086 electric_raw
ren ER13090 water_raw
ren ER13097 otheru_raw
gen utilities_acc = 0
replace utilities_acc = 1 if gas_raw >= 9999999 | electric_raw >= 9999999 | water_raw >= 9999999 | otheru_raw >= 9999999 


rename ER13087 electric_per
rename ER13089 gas_per
rename ER13091 water_per
rename ER13098 miscutils_per


ren ER13192 carinsper

ren ER16515B7 vehicle_loan
ren ER13121 vehicle_loan_raw1 
ren ER13151 vehicle_loan_raw2
ren ER13181 vehicle_loan_raw3
gen vehicle_loan_acc = 0
replace vehicle_loan_acc = 1 if vehicle_loan_raw1 >=999998 | vehicle_loan_raw2 >=999998 | vehicle_loan_raw3 >=999998 

ren ER16515B8 vehicle_down
ren ER13118 vehicle_down_raw1
ren ER13148 vehicle_down_raw2
ren ER13178 vehicle_down_raw3
gen vehicle_down_acc = 0
replace vehicle_down_acc = 1 if vehicle_down_raw1 >= 999998 | vehicle_down_raw2 >= 999998 | vehicle_down_raw3 >= 999998 

ren ER16515B9 vehicle_lease
ren ER13126 vehicle_lease_raw1
ren ER13156 vehicle_lease_raw2
ren ER13186 vehicle_lease_raw3
gen vehicle_lease_acc = 0
replace vehicle_lease_acc = 1 if vehicle_lease_raw1 >=999998 | vehicle_lease_raw2 >=999998 |vehicle_lease_raw3 >=999998 

ren ER16515C1 auto_ins
ren ER13191 auto_ins_raw
gen auto_ins_acc = 0
replace auto_ins_acc = 1 if auto_ins_raw >= 999998

ren ER16515C2 other_vehicle
ren ER13194 other_vehicle_raw
gen other_vehicle_acc = 0
replace other_vehicle_acc  = 1 if other_vehicle_raw >=999998

ren ER16515C4 gasoline
ren ER13196 gasoline_raw
gen gasoline_acc = 0
replace gasoline_acc = 1 if gasoline_raw >= 99998

ren ER16515C3 auto_repair
ren ER13195 auto_repair_raw
gen auto_repair_acc = 0
replace auto_repair_acc = 1 if auto_repair_raw >=99998

ren ER16515C5 parking
ren ER13197 parking_raw
gen parking_acc = 0
replace parking_acc = 1 if parking_raw >= 99998 

ren ER16515C6 bus_train
ren ER13198 bus_train_raw
gen bus_train_acc = 0
replace bus_train_acc = 1 if bus_train_raw >= 99998

ren ER16515C7 taxi
ren ER13199 taxi_raw
gen taxi_acc = 0
replace taxi_acc = 1 if taxi_raw >= 99998

ren ER16515C8 other_trans
ren ER13200 other_trans_raw
gen other_trans_acc = 0
replace other_trans_acc = 1 if other_trans_raw >=99998

ren ER16515C9 education
ren ER13202 education_raw1
ren ER13204 education_raw2
gen education_acc = 0
replace education_acc = 1 if education_raw1 >= 999998 | education_raw2 >= 999998

ren ER16515D1 childcare
ren ER14232 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

ren ER16515D6 health_ins


/// INCOME ///

ren ER16462 y
gen truncy=y<1
replace y=1 if y<1

ren ER16452 tyhw
ren ER16454 trhw
ren ER16456 tyoth
ren ER16458 troth
ren ER16460 soc_sectot

* Asset Income *
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end

doit, var(renty2) varamt(ER14479) varper(ER14480) varstart(ER14481) varend(ER14492)
doit, var(divi2) varamt(ER14494) varper(ER14495) varstart(ER14496) varend(ER14507)
doit, var(diviw2) varamt(ER14790) varper(ER14791) varstart(ER14792) varend(ER14803)
doit, var(interest2) varamt(ER14509) varper(ER14510) varstart(ER14511) varend(ER14522)
doit, var(interestw2) varamt(ER14805) varper(ER14806) varstart(ER14807) varend(ER14818)
doit, var(trustinc2) varamt(ER14524) varper(ER14525) varstart(ER14526) varend(ER14537)
doit, var(trustincw2) varamt(ER14820) varper(ER14821) varstart(ER14822) varend(ER14833)



ren ER16491 businc
ren ER16512 busincw

* Generate labor and asset part of farm income *
*income:

gen farmlabor=0
replace farmlabor=0.5*ER16448 			if ER16448>0
replace farmlabor=. 					if ER16448>9999997

gen farmasset=0
replace farmasset=0.5*ER16448 			if ER16448>0
replace farmasset=ER16448 				if ER16448<0
replace farmasset=. 					if ER16448>9999997

* Hours Worked *
ren ER16471 hours
ren ER16482 hourw

*Labor Income (no farm in 1994 and onward)*
ren ER16463 wages
*Labor Income (no farm in 1994 and onward)*
ren ER16465 wagesw
ren ER16490 y_business
ren ER16511 yw_business

*change these for 1993 and before
egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)

ren ER15780 hinsurance
ren ER15781 nurse
ren ER15787 doctor
ren ER15793 prescription
ren ER15799 totalhealth
ren ER13210 self

/// Prep variables to save ///
*garden market income
ren ER16505 gardasset
ren ER14461 roomerinc
ren ER14462 roomerper

ren ER14985 chsupp_paid
/// Prep variables to save ///

#delimit;

drop 

ER* S4*

; 

#delimit cr

gen year = 1999

save "$temp\HH_1999.dta", replace




********************************************************************************************
* 1997
********************************************************************************************

use "$data\1997_H", clear

ren ER10072 moved
ren ER10002 id
ren ER10008 fsize
ren ER10009 age
ren ER10010 sex
ren ER10011 agew
ren ER10012 kids
ren ER10013 newborn
ren ER10016 marit
ren ER11848 race1
ren ER11849 race2
ren ER11760 wrace1
ren ER11761 wrace2
ren ER11708 outkid
ren ER10036 house
ren ER12221B smsa  
ren ER10004A fchg
ren ER12221 state
ren ER12084 weight 
ren ER10081 empst1
ren ER10563 wempst1
ren ER12222 educ
ren ER12223 weduc

*Dropping weeks of work, years, months, weeks of experience
ren ER10199 unemplayoff
ren ER11383 tr_unemraw
ren ER11384 tr_unemper
ren ER11494 tr_unemwraw
ren ER11495 tr_unemwper
ren ER11398 tr_compraw
ren ER11399 tr_compper
ren ER11764 sp_veteran
ren ER11852 veteran
ren ER12085 occupation

ren ER11724 disab
ren ER11725 twdisab 
ren ER11726 hmdisab

ren ER11728 sp_disab
ren ER11729 sp_twdisab
ren ER11730 sp_hmdisab

ren ER11897 workyears
ren ER11898 workyears_full
ren ER11810 spworkyears_full 
ren ER11809 spworkyears

replace unemplayoff =. if unemplayoff == 8 | unemplayoff == 9
replace unemplayoff =0 if unemplayoff == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99

***********
/* No data on wife pension pre 2011 */ 

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=11052 
        while `i' <= 11063  {
            replace ER`i'=. if ER`i'>1
        	local i=`i'+1
       }
end
qui doit

egen nm=rsum(ER11052-ER11063)

*below are food stamps in average reference period
 
*with ref period:
 

replace fstmp=ER11050              if ER11051 ==6 
replace fstmp=ER11050*nm           if ER11051 ==5 
replace fstmp=ER11050*((26/12)*nm) if ER11051 ==4  
replace fstmp=ER11050*((52/12)*nm) if ER11051 ==3
replace fstmp=.					 if ER11050>99997 | ER11051 >6 | ER11051 ==1 
drop nm

*ren [86]V12779 [87]V13881 [88]V14896 [89]V16396 [90]V17812 [91]V19112 [92]V20412 [93]V21728 [01]ER18389 [03]ER21655 [05]ER25657 fstmp_imp
*replace fstmp = . if fstmp_imp == 1

*below is spending on food at home if food stamps
ren ER11068 fdhm1

ren ER11069 fdhmpER1
*below is spending on food at home, if not food stamps
ren ER11076 fdhm2
ren ER11077 fdhmpER2
*below is cost of food delivered through food stamps
ren ER11071 fddel1
ren ER11072 fddelpER1
*below is cost of food delivered without food stamps
ren ER11079 fddel2
ren ER11080 fddelpER2
*below is cost of eating out, if food stamps
ren ER11073 fout1
ren ER11074 foutpER1
*below is cost of eating out, if not food stamps
ren ER11081 fout2
ren ER11082 foutpER2
* Note that these are taken from the imputed values, _acc variables denote imputed entries
*they combine the 
ren ER10046 mortgage_raw1
ren ER10047 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER10060 rent_raw
ren ER10061 rent_per
gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER10037 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER10038 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren ER11048 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

/// INCOME ///

ren ER12079 y
gen truncy=y<1
replace y=1 if y<1

ren ER12069 tyhw
ren ER12071 trhw
ren ER12073 tyoth
ren ER12075 troth

ren ER12077 soc_sectot

* Asset Income *
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end

doit, var(renty2) varamt(ER11213) varper(ER11214) varstart(ER11215) varend(ER11226)
doit, var(divi2) varamt(ER11228) varper(ER11229) varstart(ER11230) varend(ER11241)
doit, var(diviw2) varamt(ER11524) varper(ER11525) varstart(ER11526) varend(ER11537)
doit, var(interest2) varamt(ER11243) varper(ER11244) varstart(ER11245) varend(ER11256)
doit, var(interestw2) varamt(ER11539) varper(ER11540) varstart(ER11541) varend(ER11552)
doit, var(trustinc2) varamt(ER11258) varper(ER11259) varstart(ER11260) varend(ER11271)
doit, var(trustincw2) varamt(ER11554) varper(ER11555) varstart(ER11556) varend(ER11567)


ren ER12194 businc
ren ER12215 busincw

* Generate labor and asset part of farm income *
*income:

gen farmlabor=0
replace farmlabor=0.5*ER12065 			if ER12065>0
replace farmlabor=. 					if ER12065>9999997

gen farmasset=0
replace farmasset=0.5*ER12065 			if ER12065>0
replace farmasset=ER12065 				if ER12065<0
replace farmasset=. 					if ER12065>9999997

* Hours Worked *
ren ER12174 hours
ren ER12185 hourw

*Labor Income (no farm in 1994 and onward)*
ren ER12080 wages
*Labor Income (no farm in 1994 and onward)*
ren ER12082 wagesw
ren ER12193 y_business
ren ER12214 yw_business

replace yw_business = . if yw_business >= 999997
replace y_business = . if y_business >= 999997

*change these for 1993 and before
egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)

ren ER10086 self

/// Prep variables to save ///
*garden market income
ren ER12208 gardasset
*garden market, labor part:
ren ER11195 roomerinc
ren ER11196 roomerper
ren ER11717 chsupp_paid
/// Prep variables to save ///

#delimit;

drop 

ER* 
; 

#delimit cr


gen year = 1997
save "$temp\HH_1997.dta", replace





********************************************************************************************
* 1996
********************************************************************************************

use "$data\1996_H", clear


ren ER7155 moved
ren ER7002 id
ren ER7005 fsize
ren ER7006 age
ren ER7007 sex
ren ER7008 agew
ren ER7009 kids
ren ER7010 newborn
ren ER7013 marit
ren ER9060 race1
ren ER9061 race2
ren ER8999 wrace1
ren ER9000 wrace2
ren ER8826 outkid
ren ER7032 house
ren ER9248B smsa  
ren ER7004A fchg
ren ER9247 state
ren ER9251 weight 
ren ER7163 empst1
ren ER7657 wempst1
*1991 onward:
ren ER9249 educ
*1991 onward:
ren ER9250 weduc

*Dropping weeks of work, years, months, weeks of experience
ren ER7283 unemplayoff
ren ER8489 tr_unemraw
ren ER8490 tr_unemper
ren ER8612 tr_unemwraw
ren ER8613 tr_unemwper
ren ER8504 tr_compraw
ren ER8505 tr_compper
ren ER9002 sp_veteran
ren ER9063  veteran
ren ER9108 occupation

ren ER8970  disab
ren ER8971 twdisab 
ren ER8972 hmdisab

ren ER8975 sp_disab
ren ER8976 sp_twdisab
ren ER8977 sp_hmdisab

ren ER9101 workyears
ren ER9102 workyears_full
ren ER9032 spworkyears_full 
ren ER9031 spworkyears

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99

/// ASSETS ///
/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=8158 
        while `i' <= 8169  {
            replace ER`i'=. if ER`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(ER8158-ER8169)

*below are food stamps in average reference period
 
*with ref period:
 

replace fstmp=ER8156              if ER8157 ==6 
replace fstmp=ER8156*nm           if ER8157 ==5 
replace fstmp=ER8156*((26/12)*nm) if ER8157 ==4  
replace fstmp=ER8156*((52/12)*nm) if ER8157 ==3
replace fstmp=.					 if ER8156>99997 | ER8157 >6 | ER8157 ==1 
drop nm

*ren [86]V12779 [87]V13881 [88]V14896 [89]V16396 [90]V17812 [91]V19112 [92]V20412 [93]V21728 [01]ER18389 [03]ER21655 [05]ER25657 fstmp_imp
*replace fstmp = . if fstmp_imp == 1

*below is spending on food at home if food stamps
ren ER8174 fdhm1

ren ER8175 fdhmpER1
*below is spending on food at home, if not food stamps
ren ER8181 fdhm2
ren ER8182 fdhmpER2
*below is cost of food delivered through food stamps
ren ER8177 fddel1
ren ER8178 fddelpER1
*below is cost of food delivered without food stamps
ren ER8184 fddel2
ren ER8185 fddelpER2
*below is cost of eating out, if food stamps
ren ER8179 fout1
ren ER8180 foutpER1
*below is cost of eating out, if not food stamps
ren ER8186 fout2
ren ER8187 foutpER2
* Note that these are taken from the imputed values, _acc variables denote imputed entries
*they combine the 
*note this is only done for us 1999+, even though structure begins in 1994.
ren ER7044 mortgage_raw1
ren ER7045 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER7121 rent_raw
ren ER7122 rent_per
gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER7033 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER7034 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren ER8154 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

/// INCOME ///

ren ER9244 y
gen truncy=y<1
replace y=1 if y<1

ren ER9237 tyhw
ren ER9238 trhw
ren ER9241 tyoth
ren ER9242 troth
ren ER9243 soc_sectot

* Asset Income *
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end

doit, var(renty2) varamt(ER8320) varper(ER8321) varstart(ER8322) varend(ER8333)
doit, var(divi2) varamt(ER8335) varper(ER8336) varstart(ER8337) varend(ER8348)
doit, var(diviw2) varamt(ER8642) varper(ER8643) varstart(ER8644) varend(ER8655)
doit, var(interest2) varamt(ER8350) varper(ER8351) varstart(ER8352) varend(ER8363)
doit, var(interestw2) varamt(ER8657) varper(ER8658) varstart(ER8659) varend(ER8670)
doit, var(trustinc2) varamt(ER8365) varper(ER8366) varstart(ER8367) varend(ER8378)
doit, var(trustincw2)varamt(ER8672) varper(ER8673) varstart(ER8674) varend(ER8685)

ren ER9211 businc
ren ER9233 busincw

* Generate labor and asset part of farm income *
*income:
gen farmlabor=0
replace farmlabor=0.5*ER9208 			if ER9208>0
replace farmlabor=. 					if ER9208>9999997

gen farmasset=0
replace farmasset=0.5*ER9208 			if ER9208>0
replace farmasset=ER9208 				if ER9208<0
replace farmasset=. 					if ER9208>9999997

* Hours Worked *
ren ER9187 hours
ren ER9198 hourw

*Labor Income (no farm in 1994 and onward)*
ren ER9231 wages
*Labor Income (no farm in 1994 and onward)*
ren ER9235 wagesw
ren ER9210 y_business
ren ER9232 yw_business

*change these for 1993 and before
egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)

ren ER7169 self

/// Prep variables to save ///
*garden market income
ren ER9225 gardasset
*garden market, labor part:
ren ER8302 roomerinc
ren ER8303 roomerper

ren ER8835 chsupp_paid
/// Prep variables to save ///

#delimit;

drop ER*

; 

#delimit cr


gen year=1996

save "$temp\HH_1996.dta", replace



********************************************************************************************
* 1995
********************************************************************************************

use "$data\1995_H", clear

ren ER5061 moved
ren ER5002 id
ren ER5005 fsize
ren ER5006 age
ren ER5007 sex
ren ER5008 agew
ren ER5009 kids
ren ER5010 newborn
ren ER5013 marit
ren ER6814 race1
ren ER6815 race2
ren ER6753 wrace1
ren ER6754 wrace2
ren ER6708 outkid
ren ER5032 house
ren ER6997B smsa  
ren ER5004A fchg
ren ER6996 state
ren ER7000 weight 
ren ER5067 empst1
ren ER5561 wempst1
*1991 onward:
ren ER6998 educ
*1991 onward:
ren ER6999 weduc

*Dropping weeks of work, years, months, weeks of experience
ren ER5187 unemplayoff
ren ER6372 tr_unemraw
ren ER6373 tr_unemper
ren ER6495 tr_unemwraw
ren ER6496 tr_unemwper
ren ER6387 tr_compraw
ren ER6388 tr_compper
ren ER6756 sp_veteran
ren ER6817 veteran
ren ER6857 occupation

ren ER6724 disab
ren ER6725 twdisab 
ren ER6726 hmdisab

ren ER6729 sp_disab
ren ER6730 sp_twdisab
ren ER6731 sp_hmdisab

ren ER6855 workyears
ren ER6856 workyears_full
ren ER6786 spworkyears_full 
ren ER6785 spworkyears



replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5


replace educ =. if educ == 99
replace weduc =. if weduc == 99

/// ASSETS ///

***********
* Note that these are taken from the imputed values, _acc variables denote imputed entries

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=6061 
        while `i' <= 6072  {
            replace ER`i'=. if ER`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(ER6061-ER6072)


replace fstmp=ER6059              if ER6060 ==6 
replace fstmp=ER6059*nm           if ER6060 ==5 
replace fstmp=ER6059*((26/12)*nm) if ER6060 ==4  
replace fstmp=ER6059*((52/12)*nm) if ER6060 ==3
replace fstmp=.					 if ER6059>99997 | ER6060 >6 | ER6060 ==1 
drop nm

*ren [86]V12779 [87]V13881 [88]V14896 [89]V16396 [90]V17812 [91]V19112 [92]V20412 [93]V21728 [01]ER18389 [03]ER21655 [05]ER25657 fstmp_imp
*replace fstmp = . if fstmp_imp == 1

*below is spending on food at home if food stamps
ren ER6077 fdhm1

ren ER6078 fdhmpER1
*below is spending on food at home, if not food stamps
ren ER6084 fdhm2
ren ER6085 fdhmpER2
*below is cost of food delivered through food stamps
ren ER6080 fddel1
ren ER6081 fddelpER1
*below is cost of food delivered without food stamps
ren ER6087 fddel2
ren ER6088 fddelpER2
*below is cost of eating out, if food stamps
ren ER6082 fout1
ren ER6083 foutpER1
*below is cost of eating out, if not food stamps
ren ER6089 fout2
ren ER6090 foutpER2

ren ER5038 mortgage_raw1
ren ER5039 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER5048 rent_raw
ren ER5049 rent_per
gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER5033 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER5034 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998


ren ER6057 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

/// INCOME ///

ren ER6993 y
gen truncy=y<1
replace y=1 if y<1

ren ER6986 tyhw
ren ER6987 trhw
ren ER6990 tyoth
ren ER6991 troth
ren ER6992 soc_sectot

* Asset Income *
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end

doit, var(renty2) varamt(ER6203) varper(ER6204) varstart(ER6205) varend(ER6216)
doit, var(divi2) varamt(ER6218) varper(ER6219) varstart(ER6220) varend(ER6231)
doit, var(diviw2) varamt(ER6525) varper(ER6526) varstart(ER6527) varend(ER6538)
doit, var(interest2) varamt(ER6233) varper(ER6234) varstart(ER6235) varend(ER6246)
doit, var(interestw2) varamt(ER6540) varper(ER6541) varstart(ER6542) varend(ER6553)
doit, var(trustinc2) varamt(ER6248) varper(ER6249) varstart(ER6250) varend(ER6261)
doit, var(trustincw2) varamt(ER6555) varper(ER6556) varstart(ER6557) varend(ER6568)

ren ER6960 businc
ren ER6982 busincw

* Generate labor and asset part of farm income *
*income:
 
gen farmlabor=0
replace farmlabor=0.5*ER6957 			if ER6957>0
replace farmlabor=. 					if ER6957>9999997

gen farmasset=0
replace farmasset=0.5*ER6957 			if ER6957>0
replace farmasset=ER6957 				if ER6957<0
replace farmasset=. 					if ER6957>9999997

* Hours Worked *
ren ER6936 hours
ren ER6947 hourw

*Labor Income (no farm in 1994 and onward)*
ren ER6980 wages
*Labor Income (no farm in 1994 and onward)*
ren ER6984 wagesw
ren ER6959 y_business
ren ER6981 yw_business

*change these for 1993 and before
egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)

ren ER5073 self

/// Prep variables to save ///
*garden market income
ren ER6974 gardasset
*garden market, labor part:
ren ER6185 roomerinc
ren ER6186 roomerper

ren ER6717 chsupp_paid
/// Prep variables to save ///

#delimit;

drop 

ER*
; 

#delimit cr


gen year=1995

save "$temp\HH_1995.dta", replace



********************************************************************************************
* 1994
********************************************************************************************

use "$data\1994_H", clear

rename ER2002 S301
merge 1:1 S301 using "$data\1994_W.dta"
rename S301 ER2002 
*Wealth information is only available for a subset in 1994
drop _merge


ren ER2062 moved
ren ER2002 id
ren ER2006 fsize
ren ER2007 age
ren ER2008 sex
ren ER2009 agew
ren ER2010 kids
ren ER2011 newborn
ren ER2014 marit
ren ER3944 race1
ren ER3945 race2
ren ER3883 wrace1
ren ER3884 wrace2
ren ER3706 outkid
ren ER2033 house
ren ER4157B smsa  
ren ER2005A fchg
ren ER4156 state
ren ER4160 weight 
ren ER2068 empst1
ren ER2562 wempst1
*1991 onward:
ren ER4158 educ
*1991 onward:
ren ER4159 weduc

*Dropping weeks of work, years, months, weeks of experience
ren ER2188 unemplayoff
ren ER3371 tr_unemraw
ren ER3372 tr_unemper
ren ER3494 tr_unemwraw
ren ER3495 tr_unemwper
ren ER3386 tr_compraw
ren ER3387 tr_compper
ren ER3886 sp_veteran
ren ER3947 veteran
ren ER4017 occupation

ren ER3854 disab
ren ER3855 twdisab 
ren ER3856 hmdisab

ren ER3859 sp_disab
ren ER3860 sp_twdisab
ren ER3861 sp_hmdisab

ren ER3985 workyears
ren ER3986 workyears_full
ren ER3916 spworkyears_full 
ren ER3915 spworkyears


replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99

/// ASSETS ///

***********
* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren S303 bus_ass
ren S303A bus_ass_acc
gen bus_debt =.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S305 cash
ren S305A cash_acc  

ren S309 real_estate
ren S309A real_estate_acc
gen real_estate_debt=.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S311 stocks
ren S311A stocks_acc

ren S313 vehicles
ren S313A vehicles_acc

ren S315 other_ass
ren S315A other_ass_acc

ren S307 other_debt
ren S307A other_debt_acc // Credit card charges, student loans, medical or legal bills, or loans from relatives?

/* ^^^^ Note "other debt" to be calculated manually after 2009*/

ren S320 home_equity
ren S320A home_equity_acc

ren S317 w_with_h
ren S317A w_with_h_acc


/* ^^^^ Constructed wealth variable, including equity. This imputed variable is constructed as
sum of values of seven asset types (ER46938, ER46942, ER46950, ER46954, ER46956, ER46960,
ER46964) net of debt value (ER46946) plus value of home equity (ER46966). All missing data
were assigned. */

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
foreach var of varlist ER3062-ER3073 {
   gen i`var'= (`var'>0 & `var'<13)
 }

egen nm = rsum(iER*)
replace nm = 12 if ER3062==13
replace nm=. if ER3059!=1

gen   fstmp=.
replace fstmp=ER3060                    if ER3061==0     /* does not receive food stamps */
replace fstmp=ER3060*nm           	if ER3061==1
replace fstmp=ER3060*((26/12)*nm) 	if ER3061==2
replace fstmp=ER3060*((52/12)*nm) 	if ER3061==3
replace fstmp = . if ER3060 >99997
drop ER3059-ER3073 iER*
drop nm

*ren [86]V12779 [87]V13881 [88]V14896 [89]V16396 [90]V17812 [91]V19112 [92]V20412 [93]V21728 [01]ER18389 [03]ER21655 [05]ER25657 fstmp_imp
*replace fstmp = . if fstmp_imp == 1

*below is spending on food at home if food stamps
ren ER3078 fdhm1

ren ER3079 fdhmpER1
*below is spending on food at home, if not food stamps
ren ER3085 fdhm2
ren ER3086 fdhmpER2
*below is cost of food delivered through food stamps
ren ER3081 fddel1
ren ER3082 fddelpER1
*below is cost of food delivered without food stamps
ren ER3088 fddel2
ren ER3089 fddelpER2
*below is cost of eating out, if food stamps
ren ER3083 fout1
ren ER3084 foutpER1
*below is cost of eating out, if not food stamps
ren ER3090 fout2
ren ER3091 foutpER2

ren ER2039 mortgage_raw1
ren ER2040 mortgage_raw2
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 | mortgage_raw2 >= 99998

ren ER2049 rent_raw
ren ER2050 rent_per
gen rent_acc = 0
replace rent_acc = 1 if rent_raw >= 99998
 
ren ER2034 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren ER2035 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren ER3058 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

/// INCOME ///

ren ER4153 y
gen truncy=y<1
replace y=1 if y<1

ren ER4146 tyhw
ren ER4147 trhw
ren ER4150 tyoth
ren ER4151 troth
ren ER4152 soc_sectot

* Asset Income *
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end

doit, var(renty2) varamt(ER3203) varper(ER3204) varstart(ER3205) varend(ER3216)
doit, var(divi2) varamt(ER3218) varper(ER3219) varstart(ER3220) varend(ER3231)
doit, var(diviw2) varamt(ER3524) varper(ER3525) varstart(ER3526) varend(ER3537)
doit, var(interest2) varamt(ER3233) varper(ER3234) varstart(ER3235) varend(ER3246)
doit, var(interestw2) varamt(ER3539) varper(ER3540) varstart(ER3541) varend(ER3552)
doit, var(trustinc2) varamt(ER3248) varper(ER3249) varstart(ER3250) varend(ER3261)
doit, var(trustincw2) varamt(ER3554) varper(ER3555) varstart(ER3556) varend(ER3567)

ren ER4120 businc
ren ER4142 busincw

* Generate labor and asset part of farm income *
*income:

gen farmlabor=0
replace farmlabor=0.5*ER4117  			if ER4117 >0
replace farmlabor=. 					if ER4117 >9999997

gen farmasset=0
replace farmasset=0.5*ER4117  			if ER4117 >0
replace farmasset=ER4117  				if ER4117 <0
replace farmasset=. 					if ER4117 >9999997

* Hours Worked *
ren ER4096 hours
ren ER4107 hourw

*Labor Income (no farm in 1994 and onward)*
ren ER4140 wages
*Labor Income (no farm in 1994 and onward)*
ren ER4144 wagesw
ren ER4119 y_business
ren ER4141 yw_business

*change these for 1993 and before
egen ly = rsum(wages farmlabor y_business) 
egen wly= rsum(wagesw yw_business)

ren ER2074 self

/// Prep variables to save ///
*garden market income
ren ER4134 gardasset

ren ER3185 roomerinc
ren ER3186 roomerper

ren ER3715 chsupp_paid
/// Prep variables to save ///

#delimit;

drop 


ER* S3*

; 

#delimit cr


gen year=1994

save "$temp\HH_1994.dta", replace





********************************************************************************************
* 1993
********************************************************************************************

use "$data\1993_H", clear


ren V22441 moved
ren V21602 id
ren V22405 fsize
ren V22406 age
ren V22407 sex
ren V22408 agew
ren V22409 kids
ren V22410 newborn
ren V22412 marit
ren V23276 race1
ren V23277 race2
ren V23212 wrace1
ren V23213 wrace2
ren V23171 outkid
ren V21610 house
ren V21605 smsa  
ren V21608 fchg
ren V21603 state
ren V23361 weight 
ren V22448 empst1
ren V22801 wempst1
*1991 onward:
ren V23333 educ
*1991 onward:
ren V23334 weduc

*Dropping weeks of work, years, months, weeks of experience
ren V22569 unemplayoff
ren V22108 tr_unem
ren V22094 tr_unemraw
ren V22095 tr_unemper
ren V22204 tr_unemw
ren V22190 tr_unemwraw
ren V22191 tr_unemwper
ren V22124 tr_comp
ren V22110 tr_compraw
ren V22111 tr_compper
ren V23214 sp_veteran
ren V23278 veteran
ren V22456 occupation

ren V22109 tr_unem_acc
ren V22205 tr_unemw_acc
ren V22125 tr_comp_acc

ren V23181 disab
ren V23182 twdisab 
ren V23183 hmdisab

ren V23188 sp_disab
ren V23189 sp_twdisab
ren V23190 sp_hmdisab

ren V23316 workyears
ren V23317 workyears_full
ren V23244 spworkyears_full 
ren V23243 spworkyears

ren V22012 typen
ren V22286 sp_typen

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5


replace educ =. if educ == 99
replace weduc =. if weduc == 99

/* No data on wife pension pre 2011 */ 

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=21715 
        while `i' <= 21726  {
            replace V`i'=. if V`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(V21715-V21726)

*below are food stamps in average reference period

*with ref period:
 

replace fstmp=V21713               if V21714 ==6 
replace fstmp=V21713*nm           if V21714 ==5 
replace fstmp=V21713*((26/12)*nm) if V21714 ==4  
replace fstmp=V21713*((52/12)*nm) if V21714 ==3
replace fstmp=.					 if V21713>9997 | V21714 >6 | V21714 ==1 
drop nm

*ren [86]V12779 [87]V13881 [88]V14896 [89]V16396 [90]V17812 [91]V19112 [92]V20412 [93]V21728 [01]ER18389 [03]ER21655 [05]ER25657 fstmp_imp
*replace fstmp = . if fstmp_imp == 1

*below is spending on food at home, EXCLUDING food stamp income:
ren V21707 fdhm3
ren V21708 fdhm3_imp
*Below is cost of eating out, NOT EXCLUDING food stamps 
ren V21711 fout3

ren V21614 mortgage_raw1
gen mortgage_acc = 0
replace mortgage_acc = 1 if mortgage_raw1 >= 99998 

ren V21620 rent_raw
ren V21621 rent_per
gen rent_acc = 0
replace rent_raw = . if rent_raw >= 9998
 
ren V21617 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren V21619 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren V23159 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

/// INCOME ///

ren V23322 y
gen truncy=y<1
replace y=1 if y<1

ren V21959 tyhw
ren V22366 trhw
ren V22373 tyoth
ren V22396 troth
ren V22027 soc_sec
ren V22301 soc_secw
ren V22380 soc_secyoth

* Asset Income *
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end


ren V21829 renty
doit, var(renty2) varamt(V21815) varper(V21816) varstart(V21817) varend(V21828)

ren V21845 divi
doit, var(divi2) varamt(V21831 ) varper(V21832) varstart(V21833) varend(V21844)

ren V21909 diviw
doit, var(diviw2) varamt(V21895) varper(V21896) varstart(V21897) varend(V21908)

ren V21861 interest
doit, var(interest2) varamt(V21847) varper(V21848) varstart(V21849) varend(V21860)

ren V21925 interestw
doit, var(interestw2) varamt(V21911) varper(V21912) varstart(V21913) varend(V21924)

ren V21877 trustinc
doit, var(trustinc2) varamt(V21863) varper(V21864) varstart(V21865) varend(V21876)

ren V21941 trustincw
doit, var(trustincw2) varamt(V21927) varper(V21928) varstart(V21929) varend(V21940)

ren V21810 businc
ren V21814 busincw

* Generate labor and asset part of farm income *

gen farmlabor=0
replace farmlabor=0.5*V21731 			if V21731>0
replace farmlabor=. 					if V21731>9999997

gen farmasset=0
replace farmasset=0.5*V21731 			if V21731>0
replace farmasset=V21731 				if V21731<0
replace farmasset=. 					if V21731>9999997

*income (only positive):


gen farmlabor2=0
replace farmlabor2=0.5*V21733 			if V21733>0
replace farmlabor2=. 					if V21733>9999997

gen farmasset2=0
replace farmasset2=0.5*V21733 			if V21733>0
replace farmasset2=V21733 				if V21733<0
replace farmasset2=. 					if V21733>9999997

* Hours Worked *
ren V21634 hours
ren V21670 hourw

*Labor Income (no farm in 1994 and onward)*
ren V23323 wages
*Labor Income (no farm in 1994 and onward)*
ren V23324 wagesw
ren V21738 y_business
ren V21806 yw_business

*change these for 1993 and before
egen ly = rsum(wages ) 
egen wly= rsum(wagesw )

ren V22451 self

/// Prep variables to save ///
*garden market income
ren V21781 gardasset
*garden market, labor part:
ren V21783 gardasset_labor

ren V21784 roomerinc
ren V21785 roomerper
ren V21800 roomerinclabor

ren V21961 chsupp_paid
/// Prep variables to save ///

#delimit;

drop

V* 
; 

#delimit cr


gen year=1993

save "$temp\HH_1993.dta", replace













********************************************************************************************
* 1992
********************************************************************************************

use "$data\1992_H", clear


ren V20687 moved
ren V20302 id
ren V20650 fsize
ren V20651 age
ren V20652 sex
ren V20653 agew
ren V20654 kids
ren V20655 newborn
ren V20657 marit
ren V21420 race1
ren V21421 race2
ren V21355 wrace1
ren V21356 wrace2
ren V21312 outkid
ren V20324 house
ren V20306 smsa  
ren V20310 fchg
ren V20303 state
ren V21547 weight 
ren V20693 empst1
ren V20995 wempst1
*1991 onward:
ren V21504 educ
*1991 onward:
ren V21505 weduc

*Dropping weeks of work, years, months, weeks of experience
ren V20792 unemplayoff
ren V20472 tr_unem
ren V20492 tr_unemw
ren V20473 tr_comp
ren V21357 sp_veteran
ren V21422 veteran
ren V20701 occupation

ren V21322 disab
ren V21323 twdisab 
ren V21324 hmdisab

ren V21329 sp_disab
ren V21330 sp_twdisab
ren V21331 sp_hmdisab

ren V21460 workyears
ren V21461 workyears_full
ren V21387 spworkyears_full 
ren V21386 spworkyears

ren V20466 typen
ren V20487 sp_typen

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99

ren V20333 rent_raw
ren V20334 rent_acc


/// ASSETS ///

***********
* Note that these are taken from the imputed values, _acc variables denote imputed entries


/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=20414 
        while `i' < 20425  {
            replace V`i'=. if V`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(V20414-V20425)

*below are food stamps over the year:
ren V20411 fstmp2
replace fstmp2 = . if fstmp2>999997

*ren [86]V12779 [87]V13881 [88]V14896 [89]V16396 [90]V17812 [91]V19112 [92]V20412 [93]V21728 [01]ER18389 [03]ER21655 [05]ER25657 fstmp_imp
*replace fstmp = . if fstmp_imp == 1

*below is spending on food at home, EXCLUDING food stamp income:
ren V20407 fdhm3
ren V20408 fdhm3_imp
*Below is cost of eating out, NOT EXCLUDING food stamps 
ren V20409 fout3
* Note that these are taken from the imputed values, _acc variables denote imputed entries
*they combine the 
*note this is only done for us 1999+, even though structure begins in 1994.
ren V20330 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren V20332 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren V21300 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

/// INCOME ///

ren V21481 y
gen truncy=y<1
replace y=1 if y<1

ren V20451 tyhw
ren V20501 trhw
ren V20601 tyoth
ren V20616 troth
ren V20465 soc_sec
ren V20486 soc_secw
ren V20608 soc_secyoth


* Asset Income *
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end


ren V20444 renty

* Generate labor and asset part of farm income *
*income (only positive):

gen farmlabor=0
replace farmlabor=0.5*V20427 			if V20427>0
replace farmlabor=. 					if V20427>9999997

gen farmasset=0
replace farmasset=0.5*V20427 			if V20427>0
replace farmasset=V20427 				if V20427<0
replace farmasset=. 					if V20427>9999997

ren farmlabor farmlabor2
ren farmasset farmasset2

* Hours Worked *
ren V20344 hours
ren V20374 hourw

*Labor Income (no farm in 1994 and onward)*
ren V21484 wages
*Labor Income (no farm in 1994 and onward)*
ren V20436 wagesw
ren V20428 y_business

*change these for 1993 and before
egen ly = rsum(wages ) 
egen wly= rsum(wagesw )

ren V20696 self

/// Prep variables to save ///
*garden market, labor part:
ren V20433 gardasset_labor
ren V20440 gardasset_asset

ren V20441 rommerincasset
ren V20434 roomerinclabor

ren V20453 chsupp_paid
/// Prep variables to save ///

#delimit;

drop V*

; 

#delimit cr


gen year=1992

save "$temp\HH_1992.dta", replace






********************************************************************************************
* 1991
********************************************************************************************



use "$data\1991_H", clear


ren V19387 moved
ren V19002 id
ren V19348 fsize
ren V19349 age
ren V19350 sex
ren V19351 agew
ren V19352 kids
ren V19353 newborn
ren V19355 marit
ren V20114 race1
ren V20115 race2
ren V20049 wrace1
ren V20050 wrace2
ren V20012 outkid
ren V19024 house
ren V19006 smsa  
ren V19010 fchg
ren V19003 state
ren V20243 weight 
ren V19393 empst1
ren V19695  wempst1
*1991 onward:
ren V20198 educ
*1991 onward:
ren V20199 weduc

*Dropping weeks of work, years, months, weeks of experience
ren V19492 unemplayoff
ren V19172 tr_unem
ren V19192 tr_unemw
ren V19173 tr_comp
ren V20051 sp_veteran
ren V20116  veteran
ren V19401 occupation

ren V20022 disab
ren V20023 twdisab 
ren V20024 hmdisab

ren V20028 sp_disab
ren V20029 sp_twdisab
ren V20030 sp_hmdisab

ren V20154 workyears
ren V20155 workyears_full
ren V20081 spworkyears_full 
ren V20080 spworkyears

ren V19166 typen
ren V19187 sp_typen

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99

ren V19033 rent_raw
ren V19034 rent_acc


/// ASSETS ///


/* No data on wife pension pre 2011 */ 

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=19114 
        while `i' <= 19125  {
            replace V`i'=. if V`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(V19114-V19125)

*below are food stamps over the year:

ren V19111 fstmp2
replace fstmp2 = . if fstmp2 >999997

*ren [86]V12779 [87]V13881 [88]V14896 [89]V16396 [90]V17812 [91]V19112 [92]V20412 [93]V21728 [01]ER18389 [03]ER21655 [05]ER25657 fstmp_imp
*replace fstmp = . if fstmp_imp == 1

*below is spending on food at home, EXCLUDING food stamp income:
ren V19107 fdhm3
ren V19108 fdhm3_imp
*Below is cost of eating out, NOT EXCLUDING food stamps 
ren V19109 fout3
* Note that these are taken from the imputed values, _acc variables denote imputed entries
*they combine the 
*note this is only done for us 1999+, even though structure begins in 1994.
ren V19030 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren V19032 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998

ren V20000 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

/// INCOME ///

ren V20175 y
gen truncy=y<1
replace y=1 if y<1

ren V19151 tyhw
ren V19201 trhw
ren V19301 tyoth
ren V19316 troth
ren V19165 soc_sec
ren V19186 soc_secw
ren V19308 soc_secyoth


* Asset Income *
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end


ren V19144 renty

* Generate labor and asset part of farm income *
*income (only positive):

gen farmlabor=0
replace farmlabor=0.5*V19127 			if V19127>0
replace farmlabor=. 					if V19127>9999997

gen farmasset=0
replace farmasset=0.5*V19127 			if V19127>0
replace farmasset=V19127 				if V19127<0
replace farmasset=. 					if V19127>9999997

ren farmlabor farmlabor2
ren farmasset farmasset2

* Hours Worked *
ren V19044 hours
ren V19074 hourw

*Labor Income (no farm in 1994 and onward)*
ren V20178 wages
*Labor Income (no farm in 1994 and onward)*
ren V19136 wagesw
ren V19128 y_business

*change these for 1993 and before
egen ly = rsum(wages ) 
egen wly= rsum(wagesw)

ren V19396 self

/// Prep variables to save ///
*garden market, labor part:
ren V19133 gardasset_labor
ren V19140 gardasset_asset

ren V19141 rommerincasset
ren V19134 roomerinclabor

ren V19153 chsupp_paid
/// Prep variables to save ///

#delimit;

drop

V*

; 

#delimit cr


gen year=1991

save "$temp\HH_1991.dta", replace





********************************************************************************************
* 1990
********************************************************************************************

use "$data\1990_H", clear

ren V18087 moved
ren V17702 id
ren V18048 fsize
ren V18049 age
ren V18050 sex
ren V18051 agew
ren V18052 kids
ren V18053 newborn
ren V18055 marit
ren V18814 race1
ren V18815 race2
ren V18749 wrace1
ren V18750 wrace2
ren V18712 outkid
ren V17724 house
ren V17706 smsa  
ren V17710 fchg
ren V17703 state
ren V18943 weight 
ren V18093 empst1
ren V18395 wempst1
*1986 to 1990:
ren V18898 educ_cat
*1986 to 1990:
ren V18899 weduc_cat

*Dropping weeks of work, years, months, weeks of experience
ren V18192 unemplayoff
ren V17872 tr_unem
ren V17892 tr_unemw
ren V17873 tr_comp
ren V18751 sp_veteran
ren V18816  veteran
ren V18101 occupation

ren V18722 disab
ren V18723 twdisab 
ren V18724 hmdisab

ren V18728 sp_disab
ren V18729 sp_twdisab
ren V18730 sp_hmdisab

ren V18854 workyears
ren V18855 workyears_full
ren V18781 spworkyears_full 
ren V18780 spworkyears

ren V17866 typen
ren V17887 sp_typen

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99

ren V17733 rent_raw
ren V17734 rent_acc


/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=17814 
        while `i' < 17825 {
            replace V`i'=. if V`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(V17814-V17825)

*below are food stamps over the year:
ren V17811 fstmp2
replace fstmp2=.					 if fstmp2>999997 
drop nm

*ren [86]V12779 [87]V13881 [88]V14896 [89]V16396 [90]V17812 [91]V19112 [92]V20412 [93]V21728 [01]ER18389 [03]ER21655 [05]ER25657 fstmp_imp
*replace fstmp = . if fstmp_imp == 1
*below is spending on food at home, EXCLUDING food stamp income:
ren V17807 fdhm3
ren V17808 fdhm3_imp
*Below is cost of eating out, NOT EXCLUDING food stamps 
ren V17809 fout3

ren V17730 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

ren V17732 homeinsure_raw
gen homeinsure_acc = 0
replace homeinsure_acc = 1 if homeinsure_raw>=9998


ren V18700 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998


/// INCOME ///

ren V18875 y
gen truncy=y<1
replace y=1 if y<1

ren V17851 tyhw
ren V17901 trhw
ren V18001 tyoth
ren V18016 troth
ren V17865 soc_sec
ren V17886 soc_secw
ren V18008 soc_secyoth


* Asset Income *
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end


ren V17844 renty

* Generate labor and asset part of farm income *
*income (only positive):
 

gen farmlabor=0
replace farmlabor=0.5*V17827 			if V17827>0
replace farmlabor=. 					if V17827>9999997

gen farmasset=0
replace farmasset=0.5*V17827 			if V17827>0
replace farmasset=V17827 				if V17827<0
replace farmasset=. 					if V17827>9999997

ren farmlabor farmlabor2
ren farmasset farmasset2

* Hours Worked *
ren V17744 hours
ren V17774 hourw

*Labor Income (no farm in 1994 and onward)*
ren V18878 wages
*Labor Income (no farm in 1994 and onward)*
ren V17836 wagesw
ren V17828 y_business

*change these for 1993 and before
egen ly = rsum(wages) 
egen wly= rsum(wagesw)

ren V18096 self

/// Prep variables to save ///
*garden market, labor part:
ren V17833 gardasset_labor
ren V17840 gardasset_asset

ren V17841 rommerincasset
ren V17834 roomerinclabor

ren V17853 chsupp_paid
/// Prep variables to save ///

#delimit;

drop 

V*

; 

#delimit cr


gen year=1990

save "$temp\HH_1990.dta", replace





********************************************************************************************
* 1989
********************************************************************************************

use "$data\1989_H", clear
rename V16302 S201
merge 1:1 S201 using "$data\1989_W.dta"
rename S201 V16302
drop _merge

ren V16649 moved
ren V16302 id
ren V16630 fsize
ren V16631 age
ren V16632 sex
ren V16633 agew
ren V16634 kids
ren V16635 newborn
ren V16637 marit
ren V17483 race1
ren V17484 race2
ren V17418 wrace1
ren V17419 wrace2
ren V17308 outkid
ren V16324 house
ren V16306 smsa  
ren V16310 fchg
ren V16303 state
ren V17612 weight 
ren V16655 empst1
ren V16974 wempst1
*1986 to 1990:
ren V17545 educ_cat
*1986 to 1990:
ren V17546 weduc_cat

*Dropping weeks of work, years, months, weeks of experience
ren V16754 unemplayoff
ren V16456 tr_unem
ren V16476 tr_unemw
ren V16457 tr_comp
ren V17420 sp_veteran
ren V17485  veteran
ren V16663 occupation

ren V17391 disab
ren V17392 twdisab 
ren V17393 hmdisab

ren V17397 sp_disab
ren V17398 sp_twdisab
ren V17399 sp_hmdisab

ren V17523 workyears
ren V17524 workyears_full
ren V17450 spworkyears_full 
ren V17449 spworkyears

ren V16450 typen
ren V16471 sp_typen

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

replace educ =. if educ == 99
replace weduc =. if weduc == 99


/// ASSETS ///

***********
* Note that these are taken from the imputed values, _acc variables denote imputed entries

ren S203 bus_ass
ren S203A bus_ass_acc
gen bus_debt =.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S205 cash
ren S205A cash_acc  

ren S209 real_estate
ren S209A real_estate_acc
gen real_estate_debt=.
/* ^^^ Note that Prior to 2013 the equity value is given without debt/asset split */

ren S211 stocks
ren S211A stocks_acc

ren S213 vehicles
ren S213A vehicles_acc

ren S215 other_ass
ren S215A other_ass_acc

ren S207 other_debt
ren S207A other_debt_acc // Credit card charges, student loans, medical or legal bills, or loans from relatives?

/* ^^^^ Note "other debt" to be calculated manually after 2009*/

ren S220 home_equity
ren S220A home_equity_acc

ren S217 w_with_h
ren S217A w_with_h_acc



/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=16398  
        while `i' <= 16409  {
            replace V`i'=. if V`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(V16398-V16409)

*below are food stamps over the year:
ren V16395 fstmp2
replace fstmp2=.					 if fstmp2>999997  
drop nm


ren V17296 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998


/// INCOME ///

ren V17533 y
gen truncy=y<1
replace y=1 if y<1

ren V16435 tyhw
ren V16485 trhw
ren V16585 tyoth
ren V16600 troth
ren V16449 soc_sec
ren V16470 soc_secw
ren V16592 soc_secyoth

* Asset Income *
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end


ren V16428 renty
* Generate labor and asset part of farm income *
*income (only positive):
 

gen farmlabor=0
replace farmlabor=0.5*V16411 			if V16411>0
replace farmlabor=. 					if V16411>9999997

gen farmasset=0
replace farmasset=0.5*V16411 			if V16411>0
replace farmasset=V16411 				if V16411<0
replace farmasset=. 					if V16411>9999997

* Hours Worked *
ren V16335 hours
ren V16365 hourw

*Labor Income (no farm in 1994 and onward)*
ren V17534 wages
*Labor Income (no farm in 1994 and onward)*
ren V16420 wagesw
ren V16412 y_business

*change these for 1993 and before
egen ly = rsum(wages) 
egen wly= rsum(wagesw)

ren V16658 self

/// Prep variables to save ///
*garden market, labor part:
ren V16417 gardasset_labor
ren V16424 gardasset_asset

ren V16425 rommerincasset
ren V16418 roomerinclabor

ren V16437 chsupp_paid
/// Prep variables to save ///

#delimit;

drop 

V* S2*
; 

#delimit cr


gen year=1989

save "$temp\HH_1989.dta", replace




********************************************************************************************
* 1988
********************************************************************************************

use "$data\1988_H", clear

ren V15148 moved
ren V14802 id
ren V15129 fsize
ren V15130 age
ren V15131 sex
ren V15132 agew
ren V15133 kids
ren V15134 newborn
ren V15136 marit
ren V16086 race1
ren V16087 race2
ren V16021 wrace1
ren V16022 wrace2
ren V15779 outkid
ren V14824 house
ren V14806 smsa  
ren V14810 fchg
ren V14803 state
ren V16208 weight 
ren V15154 empst1
ren V15456 wempst1
*1986 to 1990:
ren V16161 educ_cat
*1986 to 1990:
ren V16162 weduc_cat

*Dropping weeks of work, years, months, weeks of experience
ren V15253 unemplayoff
ren V14956 tr_unem
ren V14976 tr_unemw
ren V14957 tr_comp
ren V16023 sp_veteran
ren V16088  veteran
ren V15162  occupation

ren V15994 disab
ren V15995 twdisab 
ren V15996 hmdisab

ren V16000 sp_disab
ren V16001 sp_twdisab
ren V16002 sp_hmdisab

ren V16126 workyears
ren V16127 workyears_full
ren V16053 spworkyears_full 
ren V16052 spworkyears

ren V14950 typen
ren V14971 sp_typen

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5



replace educ =. if educ == 99
replace weduc =. if weduc == 99

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=14898 
        while `i' <= 14909  {
            replace V`i'=. if V`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(V14898-V14909)

*below are food stamps over the year:
ren V14895 fstmp2
replace fstmp2=.					 if fstmp2>999997 
drop nm

ren V15761 childcare_raw
gen childcare_acc = 0
replace childcare_raw = 1 if childcare_raw >= 999998

/// INCOME ///

ren V16144 y
gen truncy=y<1
replace y=1 if y<1

ren V14935 tyhw
ren V14985 trhw
ren V15085 tyoth
ren V15100 troth
ren V14949 soc_sec
ren V14970 soc_secw
ren V15092 soc_secyoth

* Asset Income *
capture program drop doit
program define doit
syntax, var(string) varamt(varname) varper(varname) varstart(varname) varend(varname)
foreach x of varlist `varstart'-`varend' {
replace `x' = . if `x' > 1 & `x' != .
}

gen `var' = 0
egen nm=rsum(`varstart'-`varend')
replace `var'=`varamt'              if `varper' ==6 
replace `var'=`varamt'*nm           if `varper' ==5 
replace `var'=`varamt'*((26/12)*nm) if `varper' ==4  
replace `var'=`varamt'*((52/12)*nm) if `varper' ==3
replace `var'=.					 if `varamt'>999997 | `varper' >6 | `varper' ==1 
drop nm
end


ren V14928 renty

* Generate labor and asset part of farm income *
*income (only positive):


gen farmlabor=0
replace farmlabor=0.5*V14911 			if V14911>0
replace farmlabor=. 					if V14911>9999997

gen farmasset=0
replace farmasset=0.5*V14911 			if V14911>0
replace farmasset=V14911 				if V14911<0
replace farmasset=. 					if V14911>9999997

ren farmlabor farmlabor2
ren farmasset farmasset2

* Hours Worked *
ren V14835 hours
ren V14865 hourw

*Labor Income (no farm in 1994 and onward)*
ren V16145 wages
*Labor Income (no farm in 1994 and onward)*
ren V14920 wagesw
ren V14912 y_business

*change these for 1993 and before
egen ly = rsum(wages) 
egen wly= rsum(wagesw)

ren V15157 self

/// Prep variables to save ///
*garden market, labor part:
ren V14917 gardasset_labor
ren V14924 gardasset_asset

ren V14925 rommerincasset
ren V14918 roomerinclabor

ren V14937 chsupp_paid
/// Prep variables to save ///

#delimit;

drop 

V*

; 

#delimit cr


gen year=1988

save "$temp\HH_1988.dta", replace


********************************************************************************************
* 1987
********************************************************************************************

use "$data\1987_H", clear

ren V14140 moved
ren V13702 id
ren V14113 fsize
ren V14114 age
ren V14115 sex
ren V14116 agew
ren V14117 kids
ren V14118 newborn
ren V14120 marit
ren V14612 race1
ren V14613 race2
ren V14547 wrace1
ren V14548 wrace2
ren V14504 outkid
ren V13724 house
ren V13706 smsa  
ren V13710 fchg
ren V13703 state
ren V14737 weight 
ren V14146 empst1
ren V14321 wempst1
*1986 to 1990:
ren V14687 educ_cat
*1986 to 1990:
ren V14688 weduc_cat

*Dropping weeks of work, years, months, weeks of experience
ren V14199 unemplayoff
ren V13941 tr_unem
ren V13961 tr_unemw
ren V13942 tr_comp
ren V14549 sp_veteran
ren V14614 veteran
ren V14154  occupation

ren V14515 disab
ren V14516 twdisab 
ren V14517 hmdisab

ren V14526 sp_disab
ren V14527 sp_twdisab
ren V14528 sp_hmdisab

ren V14652 workyears
ren V14653 workyears_full
ren V14579 spworkyears_full 
ren V14578 spworkyears

ren V13935 typen
ren V13956 sp_typen

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

///HEALTH MEASURES ///
ren V14518 in_hosp
ren V14519 days_in_hosp

replace educ =. if educ == 99
replace weduc =. if weduc == 99

ren V13732 rent_raw
ren V13733 rent_acc

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=13883 
        while `i' <= 13894  {
            replace V`i'=. if V`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(V13883-V13894)

*below are food stamps over the year:
ren V13880  fstmp2
replace fstmp2=.					 if fstmp2>999997 
drop nm

*below is spending on food at home, EXCLUDING food stamp income:
ren V13876 fdhm3
ren V13877 fdhm3_imp
*Below is cost of eating out, NOT EXCLUDING food stamps 
ren V13878 fout3

ren V13730 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

/// INCOME ///

ren V14670 y
gen truncy=y<1
replace y=1 if y<1

ren V13920 tyhw
ren V13970 trhw
ren V14070 tyoth
ren V14085 troth
ren V13934 soc_sec
ren V13955 soc_secw
ren V14077 soc_secyoth

* Asset Income *


ren V13913 renty

* Generate labor and asset part of farm income *
*income (only positive):
gen farmlabor=0
replace farmlabor=0.5*V13896 			if V13896>0
replace farmlabor=. 					if V13896>9999997

gen farmasset=0
replace farmasset=0.5*V13896 			if V13896>0
replace farmasset=V13896 				if V13896<0
replace farmasset=. 					if V13896>9999997

ren farmlabor farmlabor2
ren farmasset farmasset2

* Hours Worked *
ren V13745 hours
ren V13809 hourw

*Labor Income (no farm in 1994 and onward)*
ren V14671 wages
*Labor Income (no farm in 1994 and onward)*
ren V13905 wagesw
ren V13897 y_business

*change these for 1993 and before
egen ly = rsum(wages) 
egen wly= rsum(wagesw)

ren V14149 self

/// Prep variables to save ///
*garden market, labor part:
ren V13902 gardasset_labor
ren V13909 gardasset_asset

ren V13910 rommerincasset
ren V13903 roomerinclabor

ren V13922 chsupp_paid
/// Prep variables to save ///

#delimit;

drop


V*

; 

#delimit cr


gen year=1987

save "$temp\HH_1987.dta", replace





********************************************************************************************
* 1986
********************************************************************************************

use "$data\1986_H", clear


ren V13037 moved
ren V12502 id
ren V13010 fsize
ren V13011 age
ren V13012 sex
ren V13013 agew
ren V13014 kids
ren V13015 newborn
ren V13017 marit
ren V13565 race1
ren V13566 race2
ren V13500 wrace1
ren V13501 wrace2
ren V13407 outkid
ren V12524 house
ren V12506 smsa  
ren V12510 fchg
ren V12503 state
ren V13687 weight 
ren V13046 empst1
ren V13225 wempst1
*1986 to 1990:
ren V13640 educ_cat
*1986 to 1990:
ren V13641 weduc_cat

*Dropping weeks of work, years, months, weeks of experience
ren V13101 unemplayoff
ren V12839 tr_unem
ren V12859 tr_unemw
ren V12840 tr_comp
ren V13502 sp_veteran
ren V13567  veteran
ren V13054  occupation

ren V13427 disab
ren V13428 twdisab 
ren V13429 hmdisab

ren V13462 sp_disab
ren V13463 sp_twdisab
ren V13464 sp_hmdisab

ren V13605 workyears
ren V13606 workyears_full
ren V13532 spworkyears_full 
ren V13531 spworkyears

ren V12833 typen
ren V12854 sp_typen

replace unempl =. if unempl == 8 | unempl == 9
replace unempl =0 if unempl == 5

///HEALTH MEASURES ///
ren V13421 trouble_walk
ren V13422 trouble_bend
ren V13423 trouble_drive
ren V13424 need_trav_ass
ren V13425 need_stay_ins
ren V13426 stay_in_bed
ren V13430 ltd_phy_act
ren V13433 trouble_eye
ren V13435 in_hosp
ren V13436 days_in_hosp
ren V13437 height
ren V13438 bodyweight
ren V13440 exerc



replace educ =. if educ == 99
replace weduc =. if weduc == 99

ren V12532 rent_raw
ren V12533 rent_acc

/// EXPENDITURE ///
* First need to calculate foodstamps and utility subsidy
gen       fstmp=0
cap program drop doit
program def doit
        local i=12781 
        while `i' <= 12792  {
            replace V`i'=. if V`i'>1
        	local i=`i'+1
       }
end
qui doit
egen nm=rsum(V12781-V12792)

*below are food stamps over the year:
ren V12778 fstmp2
replace fstmp2 = . if fstmp2 > 999997
drop nm

*below is spending on food at home, EXCLUDING food stamp income:
ren V12774 fdhm3
ren V12775 fdhm3_imp
*Below is cost of eating out, NOT EXCLUDING food stamps 
ren V12776 fout3

ren V12530 prop_tax_raw
gen prop_tax_acc = 0
replace prop_tax_acc = 1 if prop_tax_raw >= 99998 

/// INCOME ///

ren V13623 y
gen truncy=y<1
replace y=1 if y<1

ren V12818 tyhw
ren V12868 trhw
ren V12968 tyoth
ren V12983 troth
ren V12832 soc_sec
ren V12853 soc_secw
ren V12975 soc_secyoth

* Asset Income *


ren V12811 renty

* Generate labor and asset part of farm income *
*income (only positive):
gen farmlabor=0
replace farmlabor=0.5*V12794 			if V12794>0
replace farmlabor=. 					if V12794>9999997

gen farmasset=0
replace farmasset=0.5*V12794 			if V12794>0
replace farmasset=V12794 				if V12794<0
replace farmasset=. 					if V12794>9999997

ren farmasset farmasset2
ren farmlabor farmlabor2

* Hours Worked *
ren V12545 hours
ren V12657 hourw

*Labor Income (no farm in 1994 and onward)*
ren V13624 wages
*Labor Income (no farm in 1994 and onward)*
ren V12803 wagesw
ren V12795 y_business

*change these for 1993 and before
egen ly = rsum(wages ) 
egen wly= rsum(wagesw )

ren V13049 self

/// Prep variables to save ///
*garden market, labor part:
ren V12800 gardasset_labor
ren V12807 gardasset_asset

ren V12808 rommerincasset
ren V12801 roomerinclabor

ren V12820 chsupp_paid
/// Prep variables to save ///

#delimit;

drop

V*

; 

#delimit cr


gen year=1986

save "$temp\HH_1986.dta", replace





use "$temp\HH_1986", clear

forvalues i=87(1)97{
append using "$temp\HH_19`i'.dta"
}

/*
forvalues i = 70(2)90{
append using "$output\fam`i'.dta"
}
*/

forvalues i = 1999(2)2017{
append using "$temp\HH_`i'.dta"
}
replace roomerinc =. if roomerinc >=999998
replace roomerinc = roomerinc*12 if roomerper==5
replace roomerinc = roomerinc*52 if roomerper==3
replace roomerinc = roomerinc*26 if roomerper==4
replace roomerinc = roomerinc*365 if roomerper==2
replace roomerinc = . if roomerper >6 
replace chsupp=. if chsupp>=99998

*previous year's price index value:
*source: FRED, https://fred.stlouisfed.org/series/CPIAUCSL

replace year=year-1

gen price=.
replace price=1.445 if year==1993
replace price=1.482 if year==1994
replace price=1.524 if year==1995
replace price=1.569 if year==1996
replace price=1.605 if year==1997
replace price=1.630 if year==1998
replace price=1.666 if year==1999
replace price=1.722 if year==2000
replace price=1.771 if year==2001
replace price=1.799 if year==2002
replace price=1.840 if year==2003
replace price=1.889 if year==2004
replace price=1.953 if year==2005
replace price=2.016 if year==2006
replace price=2.153 if year==2008
replace price = 2.145647 if year==2009
replace price = 2.181 if year==2010
replace price = 2.24923 if year==2011
replace price = 2.296 if year==2012
replace price = 2.329518 if year==2013
replace price = 2.367 if year==2014
replace price = 2.369978 if year==2015
replace price = 2.400 if year==2016
replace price = 2.451342 if year==2017

replace price=1.403 if year==1992
replace price=1.362 if year==1991
replace price=1.307 if year==1990
replace price=1.240 if year==1989
replace price=1.183 if year==1988
replace price=1.136 if year==1987
replace price=1.096 if year==1986
replace price=1.076 if year==1985
replace price=1.039 if year==1984
replace price=0.996 if year==1983
replace price=0.965 if year==1982
replace price=0.909 if year==1981
replace price=0.824 if year==1980
replace price=0.726 if year==1979
replace price=0.652 if year==1978
replace price=0.606 if year==1977
replace price=0.569 if year==1976
replace price=0.538 if year==1975
replace price=0.493 if year==1974
replace price=0.444 if year==1973
replace price=0.418 if year==1972
replace price=0.405 if year==1971
replace price=0.388 if year==1970
replace price=0.367 if year==1969
replace price=0.348 if year==1968
replace price=0.334 if year==1967
replace price=price/1.569			/*price in 1996$ */

replace year=year+1

save "$temp\HH_Panel.dta", replace

/*
forvalues i = 68(2)90{
erase "$output\fam`i'.dta"
}
*/

forvalues i = 2009(2)2017{
erase "$temp\HH_`i'.dta"
}

cd "$do"


